行列转换

偏执的太偏执、 2021-11-17 04:44 399阅读 0赞

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);

一、传统的行列转换

把

![1132619-20190709185036098-1762934869.png][]

转化成

![1132619-20190709185059239-1300052616.png][]

第一步：

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

获取如下表

![1132619-20190709195328093-202830892.png][]

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

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;

![1132619-20190709195718630-2063899176.png][]

简化版本

--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

转载于:https://www.cnblogs.com/lukelook/p/11160095.html

[1132619-20190709185036098-1762934869.png]: /images/20211116/c5f232830e3741b6aadeff4b0c43c303.png
[1132619-20190709185059239-1300052616.png]: /images/20211116/460cf62c673145ecba7e09d863515eba.png
[1132619-20190709195328093-202830892.png]: /images/20211116/36f5c4e4ae5741b8a9a904c2bd6e3242.png
[1132619-20190709195718630-2063899176.png]: /images/20211116/1ed36f66bcd54b4d9f72294a073b3bbf.png