Oracle 行转列、列转行详解（pivot、unpivot）

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

测试结果：（细节：列的个数必须是确定的，如：语文、数学、英语）

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;
1
2
3
4
5
6
7
8
9
10
11
12
13

测试结果：