1 概述
2 示例
2.1 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 英语))
-- where id in (1, 2, 3)
order by id;
测试结果:(细节:列的个数必须是确定的,如:语文、数学、英语)
2.2 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))
-- where id in (1, 2, 3)
order by id;
测试结果: