文章目录
pivot:行转列
测试语句:
WITH t_test AS(
SELECT '1' ID, '张三' NAME, '70' score, 'CHINESE' subject FROM dual UNION ALL
SELECT '1' ID, '张三' NAME, '90' score, 'MATH' subject FROM dual UNION ALL
SELECT '1' ID, '张三' NAME, '95' score, 'ENGLISH' subject FROM dual UNION ALL
SELECT '2' ID, '李四' NAME, '75' score, 'CHINESE' subject FROM dual UNION ALL
SELECT '2' ID, '李四' NAME, '85' score, 'MATH' subject FROM dual UNION ALL
SELECT '2' ID, '李四' NAME, '90' score, 'ENGLISH' subject FROM dual UNION ALL
SELECT '3' ID, '王五' NAME, '90' score, 'CHINESE' subject FROM dual UNION ALL
SELECT '3' ID, '王五' NAME, '90' score, 'MATH' subject FROM dual UNION ALL
SELECT '3' ID, '王五' NAME, '90' score, 'ENGLISH' subject FROM dual
)
SELECT *
FROM t_test -- 请注意: ! 表别名无效 !
pivot (SUM(score) -- 原有列值,必须是 聚合函数
FOR subject IN('CHINESE' AS 语文, 'MATH' AS 数学, 'ENGLISH' AS 英语)) -- 原有列名,必须是已知的个数,如此处就是 3 个。
-- WHERE id IN ('1', '2', '3')
ORDER BY id;
测试结果:(细节:列的个数必须是确定的,如:语文、数学、英语)
unpivot:列转行
测试语句:
WITH t_test AS (
SELECT '1' ID, '张三' NAME, 70 chinese , 90 math , 95 english FROM dual UNION ALL
SELECT '2' ID, '李四' NAME, 75 chinese , 85 math , 90 english FROM dual UNION ALL
SELECT '3' ID, '王五' NAME, 90 chinese , 90 math , 90 english FROM dual
)
SELECT ID,
NAME,
score 成绩,
subject 学科
FROM t_test -- 请注意: ! 表别名无效 !
unpivot(score FOR subject IN(chinese, math, english)) -- 新增列: score、subject
-- WHERE ID IN ('1', '2', '3')
ORDER BY ID;
测试结果: