sql如何行转列，列转行

逃离我推掉我的手 2023-06-22 13:48 28阅读 0赞

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

*  行转列

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

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>Name</td> 
   <td>Subject</td> 
   <td>Score</td> 
  </tr> 
  <tr> 
   <td>小明</td> 
   <td>语文</td> 
   <td>30</td> 
  </tr> 
  <tr> 
   <td>小明</td> 
   <td>数学</td> 
   <td>22</td> 
  </tr> 
  <tr> 
   <td>小明</td> 
   <td>英语</td> 
   <td>66</td> 
  </tr> 
  <tr> 
   <td>小花</td> 
   <td>语文</td> 
   <td>30</td> 
  </tr> 
  <tr> 
   <td>小花</td> 
   <td>数学</td> 
   <td>33</td> 
  </tr> 
  <tr> 
   <td>小花</td> 
   <td>英语</td> 
   <td>67</td> 
  </tr> 
 </tbody> 
</table>

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

这里就需要用到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 T
    group by name;

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

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

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

*  列转行

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

转换为

最常见的一种列转行操作是采用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='英语'

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

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

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