SQL row/column conversion dynamic SQL)

-- Create by ranen
-- Bolg: http://blog.csdn.net/ranen2010
-- Original article link: http://blog.csdn.net/Ranen2010/archive/2011/04/22/6341056.aspx

/*
Source Region table structure
Stuname CID score
------------------------------------------
Ranen 1 80
Ranen 2 84
Ranen 3 98
Kevin 1 74
Kevin 2 98
Kevin 3 100
Jacky 1 50
Jacky 3 96

Source curriculum structure
Id cname
-----------------------------------------
1 C #
2 Javascript
3. SQL Server

Converted table structure
Stuname C # JavaScript SQL Server
-----------------------------------------------------
Jacky 50 null 96
Kevin 74 98 100
Ranen 80 84 98

*/

Create Table coursetable
(
Id int identity primary key,
Cname nvarchar (30) not null
)
Go
Insert into coursetable values ('C #')
Insert into coursetable values ('javascript ')
Insert into coursetable values ('SQL Server ')

Create Table scoretable
(
Stuname varchar (20) not null, -- Student
CID int, -- course number
Score int -- score
)
Go
Insert into scoretable values ('ranen', 1, 80)
Insert into scoretable values ('ranen', 2, 84)
Insert into scoretable values ('ranen', 3,98)
Insert into scoretable values ('kevin)
Insert into scoretable values ('kevin ', 2, 98)
Insert into scoretable values ('kevin ', 3,100)
Insert into scoretable values ('jacky', 1, 50)
Insert into scoretable values ('jacky', 3,96)
Go
-- 2000 convert static columns to columns
Select stuname,
Max (Case cname when 'C # 'then score else 0 end) C #,
Max (Case cname when 'javascript 'Then score else 0 end) JavaScript,
Max (Case cname when 'SQL Server' then score else 0 end) sqlserver
From (
Select S. stuname, C. cname, S. score from scoretable s
Inner join coursetable C on S. cid = C. ID) T group by stuname

-- 2000 dynamic SQL
-- Insert into coursetable values ('html ') -- add a dynamic SQL statement for the Course Test
Declare @ SQL varchar (500)
Set @ SQL = 'select stuname'
Select @ SQL = @ SQL + ', max (Case cname when ''' + cname + ''' then score else 0 end) [' + cname + ']'
From (select distinct cname from coursetable) as C
Set @ SQL = @ SQL + 'from (select S. stuname, C. cname, S. score from scoretable s
Inner join coursetable C on S. cid = C. ID) T group by stuname'

Exec (@ SQL)
Go

-- 2005 static SQL
Select * from (
Select S. stuname, C. cname, S. score from scoretable s
Inner join coursetable C on S. cid = C. ID
) A round (max (score) for cname in (C #, JavaScript, [SQL Server]) B

-- 2005 dynamic SQL
Declare @ SQL varchar (500)
Select @ SQL = isnull (@ SQL + ',', '') + '[' + cname + ']' from coursetable group by cname
Exec ('select * from (
Select S. stuname, C. cname, S. score from scoretable s
Inner join coursetable C on S. cid = C. ID
) A round (max (score) for cname in ('+ @ SQL +') B ')
Go
Drop table coursetable
Drop table scoretable