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