sql如何行转列，列转行

小孬鼠于 2021-11-17 15:25:44 发布

我们都知道在sql查询中，union 是增行，join是增列，但是如果碰到需要行转列，列转行时，一味的使用 join来完成则会变的异常繁杂。如把一天24小时的行转换成列，则需要join24次，既不方便管理也使sql运行效率很慢，能脱裤子就解决的非要跑到高山游到海底

行转列
那么如何行转列呢，假如有一张表T，字段有name,subject,score,数据如下

Name	Subject	Score
小明	语文	30
小明	数学	22
小明	英语	66
小花	语文	30
小花	数学	33
小花	英语	67

转换成为 name，语文，数学，英语， 即 小明,30,30,30

Name	语文	数学	英语
小明	30	22	66
小花	30	33	67

这里就需要用到case when 以及 sum（max也可以，依据实际情况）

-- 遍历值，这里max也可以替换为sum
select name,
       max(case subject when '语文' then score else 0 end) as 语文,
       max(case subject when '数学' then score else 0 end) as 数学,
       max(case subject when '英语' then score else 0 end) as 英语
from Tgroup by name;1234567

上面是从逻辑上来做处理，但是看起来相对麻烦，系统给了一种更为简洁的方法，PIVOT

SELECT *
  FROM T
 PIVOT (
    SUM(score) FOR subject IN (语文, 数学, 英语))12345

解释：就是for 循环subject列的语文，数学，英语等值求sum，并把语文等三个值作为列名，sum（score）作为对应列内容

列转行
列转行的需求就是把上面的行专列反过来一下。即把

Name	语文	数学	英语
小明	30	22	66
小花	30	33	67

转换为

Name	Subject	Score
小明	语文	30
小明	数学	22
小明	英语	66
小花	语文	30
小花	数学	33
小花	英语	67

最常见的一种列转行操作是采用UNION (UNION ALL) 进行并列操作,比如上面的需求可写为

SELECT Name, Subject, Score FROM T WHERE Name='小明' and Subject='语文'
 UNION ALL SELECT Name, Subject, Score FROM T WHERE Name='小明' and Subject='数学'
 UNION ALL SELECT Name, Subject, Score FROM T WHERE Name='小明' and Subject='英语'
 UNION ALL SELECT Name, Subject, Score FROM T WHERE Name='小花' and Subject='语文'
 UNION ALL SELECT Name, Subject, Score FROM T WHERE Name='小花' and Subject='数学'
 UNION ALL SELECT Name, Subject, Score FROM T WHERE Name='小花' and Subject='英语'123456

上面这种写法，方便易用好理解，但是相对冗余，不建议高级开发者使用，我们可以用更高级一点语法，就是与PIVOT对应的UNPIVOT

 SELECT *
   FROM TUNPIVOT (
    score FOR subject IN (语文, 数学, 英语))12345

解释：就是for 循环subject(列名自定义)的语文，数学，英语等列求score，并把作为subject列名，语文，数学，英语作为列内容