sql 行转列/列转行 示例

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

行转列

假如我们有下表：

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
    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

           

通过上面 SQL 语句即可得到下面的结果