SQL行转列，列转行

今天在优化分组统计SQL涉及到行列互转，记录一下

行列互转如下图所示

 行转列

这是我们现在的表，如下图

通过SQL

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

得到

 通过第二种办法也可以得到上图

SELECT name,
 MAX(
 CASE
 WHEN subject='语文'
 THEN score
 ELSE 0
 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

 

列转行

这是我们现在的表，如下图

 通过SQL

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

得到

 通过第二种办法也可以得到上图

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