通过case when和pivot函数均可实现行转列
--模拟数据
CREATE TABLE Test(name VARCHAR(5),Tsource VARCHAR(5),grade INT)
insert into Test VALUES ('A','语文',56)
insert into Test VALUES ('A','数学',78)
insert into Test VALUES ('A','外语',60)
insert into Test VALUES ('B','语文',74)
insert into Test VALUES ('B','数学',84)
insert into Test VALUES ('C','数学',94)
insert into Test VALUES ('C','外语',56)
go
1:case when
SELECT name,max(CASE WHEN Tsource='语文' THEN grade ELSE 0 END) as 语文,
max(CASE WHEN Tsource='数学' THEN grade ELSE 0 END )as 数学,
max(CASE WHEN Tsource='外语' THEN grade ELSE 0 END )as 外语
FROM Test GROUP BY name
2:pivot(PIVOT 的一般语法是:PIVOT(聚合函数(列) FOR 列 in (…) )AS P)
select * from Test pivot
(MAX(grade) FOR Tsource in([语文],[数学],[外语]))t