行列转换

CREATE TEMPORARY TABLE Scores(
ID INT,
Student varchar(10),
Subject varchar(10),
Score INT
);

INSERT INTO Scores VALUES(2, '张三', '语文', 93);
INSERT INTO Scores VALUES(3, '张三', '英语', 90);
INSERT INTO Scores VALUES(5, '李四', '语文', 88);
INSERT INTO Scores VALUES(7, '李四', '英语', 78);
INSERT INTO Scores VALUES(8, '王五', '语文', 98);
INSERT INTO Scores VALUES(9, '王五', '英语', 86);

一、传统的行列转换

把

转化成

第一步：

select
 STUDENT,
 (case  subject when '语文' then score else 0 end) as 语文,
 (case  subject when '英语' then score else 0 end) as 英语                 
from scores

获取如下表

第二步按姓名聚合，取名列最大值

 select student ,max(语文) as 语文 ,max(英语) as 英语 from (
 select
 STUDENT,
 (case  subject when '语文' then score else 0 end) as 语文,
 (case  subject when '英语' then score else 0 end) as 英语                 
from scores
) group by student;

简化版本

--2，1的简化版
SELECT student as '姓名',
max(case Subject when '语文' then Score else 0 end) as '语文',
max(case Subject when '英语' then score else 0 end) as '英语'
from Scores
group by student;

--3, 当科目比较多（要转换的列较多时），使用游标生成查询语句
declare @sql varchar(8000)
set @sql = 'select student as 姓名'
select @sql = @sql + ', max(case subject when ''' + subject+ ''' then score  else 0 end) [' + subject+ ']' 
from (select distinct subject from Scores) as a 
set @sql = @sql + ' from Scores group by student' 
print @sql   --打印生成的sql
exec(@sql)   --执行该sql

二、 PIVOT (SQL Server 2005)

SELECT student, [语文], [英语]  --第三步（选择行转列后的结果集的列）这里可以用“*”表示选择所有列，也可以只选择某些列
FROM #Scores --这里是PIVOT第二步骤(准备原始的查询结果，因为PIVOT是对一个原始的查询结果集进行转换操作，所以先查询一个结果集出来)这里可以是一个select子查询，但为子查询时候要指定别名，否则语法错误
PIVOT (
    AVG(score) FOR [subject] in ([语文], [英语])  --这里是PIVOT第一步骤，也是核心的地方，进行行转列操作。聚合函数SUM表示你需要怎样处理转换后的列的值，是总和(sum)，还是平均(avg)还是min,max等等。例如如果Scores表中有两条数据并且其subject都是“语文”，其中一条的score是50,另一条score是70，那么在这里使用sum，行转列后“语文”这个列的值当然是120了。后面的FOR [subject] in ([语文], [英语])中 for [subject]就是说将subject列的值分别转换成一个个列，也就是“以值变列”。但是需要转换成列的值有可能有很多，我们只想取其中几个值转换成列，那么怎样取呢？就是在in里面了，比如我此刻只想看“语文”的分数，在in里面就只写“语文”。总的来说，AVG(score) FOR [subject] in ([语文], [英语])这句的意思如果直译出来，就是说：将列[subject]值为"语文","英语"分别转换成列，这些列的值取score的平均值。
) Scores_alias  --别名一定要写

pivot只实现了case的工作，需要我们进一步聚合去空值：

SELECT student, MAX([语文]) AS '语文', MAX([英语]) AS '英语'
FROM #Scores
PIVOT (
    AVG(score) FOR [subject] in ([语文], [英语])
) Scores_alias
GROUP BY student