sql server查询结果行转列

sql查询结果行转列是将查询数据的多行转为一列显示，更为直观。如下示例：

原始数据：

学号	姓名	课程	分数
1001	张三	语文	85
1001	张三	数学	92
1001	张三	英语	90
1002	李四	语文	88
1002	李四	数学	96

最终展示效果：

学号	姓名	语文	数学	英语
1001	张三	85	92	90
1002	李四	88	96

测试数据准备(复制运行即可）:

create table tb_test
(sno char(4) ,
sname varchar(10),
course varchar(10),
score decimal(3,1))

insert into tb_test values('1001','张三','语文',85)
insert into tb_test values('1001','张三','数学',92)
insert into tb_test values('1001','张三','英语',90)
insert into tb_test values('1002','李四','语文',88)
insert into tb_test values('1002','李四','数学',96)

1、case when 方法

select sno '学号',sname '姓名',
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 tb_test
group by sno,sname

sum 换成max 效果一样，但是这种方法只适合列较少的情况，否则几十列的话就的重复几十个case when。

2、pivot函数

select *
FROM(select sno '学号',sname '姓名',course,Score from tb_test) t1
PIVOT(max(score)FOR course IN(语文,数学,英语 )) AS t2

这种方法比第1种稍微好点，不用写那么多case when ,但还是要把课程名全部列一遍，一旦增加一个课程，就需要对sql语句进行改动，有没有一劳永逸的方法呢?

有，那就是动态的构造in 后面的字符串，请看方法3。

3、pivot动态实现

DECLARE @sql_str VARCHAR(8000)
DECLARE @sql_col VARCHAR(8000)
SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME(course) FROM tb_test GROUP BY course
SET @sql_str = '
SELECT * FROM (
select sno 学号,sname 姓名,course,score from tb_test) as t1 PIVOT
(SUM(score) FOR course IN ( '+ @sql_col +') ) AS t2
ORDER BY t2.学号,t2.姓名'
EXEC (@sql_str)

SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME(course) FROM tb_test GROUP BY course 这串代码就是将课程名全部取出去重后拼接在一起

这样既可动态实现sql查询的行转列。

SQL脚本下载

链接: https://pan.baidu.com/s/1-AaSSDZc02dgY3amAfavUg 提取码: et2a