[SQL Server] row-to-column problem summary-2. Column-to-row

If not object_id ('class') is null
Drop table class
Go
Create Table class ([Student] nvarchar (2), [mathematical] int, [physical] int, [English] int, [Chinese] INT)
Insert class
Select n 'Li si', Union all
Select n 'zhang san', 87,90, 82,78
Go

-- 2000:

Dynamic:

Declare @ s nvarchar (4000)
Select @ s = isnull (@ s + 'Union all', '') + 'select [Student], [course] = '+ quotename (name ,'''') -- isnull (@ s + 'Union all', '') removes the first union all in the string @ s.
+ ', [Score] =' + quotename (name) + 'from class'
From syscolumns where id = object_id ('class') and name not in ('student ') -- exclude columns not converted
Order by colid
Exec ('select * from ('+ @ s +') T order by [Student], [course] ') -- add a sort

Generate static:
Select *
From (select [Student], [course] = 'mat', [score] = [mathematics] from class Union all
Select [Student], [course] = 'physical ', [score] = [physical] from class Union all
Select [Student], [course] = 'English ', [score] = [English] from class Union all
Select [Student], [course] = 'China', [score] = [Chinese] from class) T
Order by [Student], [course]

Go
-- 2005:

Dynamic:

Declare @ s nvarchar (4000)
Select @ s = isnull (@ s + ',', '') + quotename (name)
From syscolumns where id = object_id ('class') and name not in ('student ')
Order by colid
Exec ('select student, [course], [score] from class Untitled ([score] for [course] In ('+ @ s +') B ')

Go
Select
Student, [course], [score]
From
Class
Unregister
([Score] for [course] In ([mathematics], [physics], [English], [Chinese]) B

Generation format:
/*
Student Course score
-------------------------
Li Si math 77
Li Si physical 85
Li Si English 65
Li Si language 65
James math 87
Zhang San physical 90
James English 82
Zhang San's speech 78

(Eight rows are affected)
*/