sql列转行

CREATETABLE[Test](
[id][int]IDENTITY(1,1)NOTNULL,
[name][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL,
[subject][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL,
[Source][numeric](18,0)NULL
)ON[PRIMARY]
GO

INSERTINTO[test]([name],[subject],[Source])values(N'张三',N'语文',60)
INSERTINTO[test]([name],[subject],[Source])values(N'李四',N'数学',70)
INSERTINTO[test]([name],[subject],[Source])values(N'王五',N'英语',80)
INSERTINTO[test]([name],[subject],[Source])values(N'王五',N'数学',75)
INSERTINTO[test]([name],[subject],[Source])values(N'王五',N'语文',57)
INSERTINTO[test]([name],[subject],[Source])values(N'李四',N'语文',80)
INSERTINTO[test]([name],[subject],[Source])values(N'张三',N'英语',100)
Go

--交叉表语句的实现：
--用于：交叉表的列数是确定的
selectname,sum(casesubjectwhen'数学'thensourceelse0end)as'数学',
sum(casesubjectwhen'英语'thensourceelse0end)as'英语',
sum(casesubjectwhen'语文'thensourceelse0end)as'语文'
fromtest
groupbyname

--用于：交叉表的列数是不确定的
declare@sqlvarchar(8000)

set@sql='selectname,'
select@sql=@sql+'sum(casesubjectwhen'''+subject+'''
thensourceelse0end)as'''+subject+''','
from(selectdistinctsubjectfromtest)asa
select@sql=left(@sql,len(@sql)-1)+'fromtestgroupbyname'
exec(@sql)
go

--用于：交叉表的列数是不确定的
declare@sqlvarchar(8000)
set@sql='selectid,'
select@sql=@sql+'(casesubjectwhen'''+subject+'''
thensourceelsenullend)as'''+subject+''','
from(selectdistinctsubjectfromtest)asa
select@sql=left(@sql,len(@sql)-1)+'fromtest'
exec(@sql)
go