sql语句实现行转列的3种方法实例

CREATE TABLE TestTable(
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [UserName] [nvarchar](50) NULL,
 [Subject] [nvarchar](50) NULL,
 [Source] [numeric](18, 0) NULL
) ON [PRIMARY]
go
INSERT INTO TestTable ([UserName],[Subject],[Source]) 
 SELECT N'张三',N'语文',60 UNION ALL
 SELECT N'李四',N'数学',70 UNION ALL
 SELECT N'王五',N'英语',80 UNION ALL
 SELECT N'王五',N'数学',75 UNION ALL
 SELECT N'王五',N'语文',57 UNION ALL
 SELECT N'李四',N'语文',80 UNION ALL
 SELECT N'张三',N'英语',100
GO

select UserName 姓名,
sum(case Subject when '语文' then Source else 0 end) 语文,sum(case Subject when '数学' then Source else 0 end) 数学,
sum(case Subject when '英语' then Source else 0 end) 英语 from TestTable group by UserName

select  from 
(select UserName,Subject,Source from TestTable) testpivot(sum(Source) for Subject in(语文,数学,英语)
) pvt

alter proc pro_test
@userImages varchar(200),
@Subject varchar(20),
@Subject1 varchar(200),
@TableName varchar(50)
as
 declare @sql varchar(max)='select  from (select '+@userImages+' from'+@TableName+') tab
pivot
(
sum('+@Subject+') for Subject('+@Subject1+')
) pvt'
exec (@sql)
go
exec pro_test 'UserName,Subject,Source',
'TestTable',
'Subject',
'语文，数学，英语'