SQL行转列，列转行

SQL 行转列，列转行
行列转换在做报表分析时还是经常会遇到的，今天就说一下如何实现行列转换吧。

行列转换就是如下图所示两种展示形式的互相转换

列转行（上面图1变图2）

SELECT name,
  MAX(CASE WHEN subject='语文' THEN score ELSE END) AS "语文",
  MAX(CASE WHEN subject='数学' THEN score ELSE 0 END) AS "数学",
  MAX(CASE WHEN subject='英语' THEN score ELSE 0 END) AS "英语"
FROM student
GROUP BY name

行转列（上面图2变图1）

SELECT
    NAME, '语文' AS subject , MAX("语文") AS score
FROM student1 GROUP BY NAME
UNION
SELECT
    NAME,  '数学' AS subject , MAX("数学") AS score
FROM student1 GROUP BY NAME
UNION
SELECT
    NAME, '英语' AS subject , MAX("英语") AS score
FROM student1 GROUP BY NAME

补充： 某些数据库可以PIVOT、UNPIVOT等函数，更加简便

参考：
https://zhuanlan.zhihu.com/p/66207434