sql 行转列

sql 行转列
CREATE TABLE Table_1(
[Id] [int] IDENTITY(1,1) NOT NULL,
[name ] nvarchar NULL,
[course] nvarchar NULL,
[score] [numeric](18, 0) NULL
) ON [PRIMARY]
go
INSERT INTO Table_1([name ],[course],[score])
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

方法1：select name ,sum(case when course=‘语文’ then score end) as 语文,
sum(case when course=‘数学’ then score end )as 数学,
sum(case when course=‘英语’ then score end )as 英语
from Table_1 group by name;

//pivot函数
–alter table Table_1 alter column score int
方法 2：select name,[语文],[数学],[英语]
from (select name,course,Score from Table_1 )as TBS
pivot
(
sum(Score)
for course in([语文],[数学],[英语])
)
as pvt
方法 3：
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 ‘name ,course,score’,
‘TestTable’,
‘Subject’,
‘语文，数学，英语’