ORACLE行转列和列转行

ORACLE行转列和列转行

定义

• 行转列：把一行多列数据转换成一列多行数据
• 列转行：把一列多行数据转换成一行多列数据

ORACLE-行转列-unpivot

数据准备

-- 此表用于表示某人四个季度的收入：用于行转列测试
create table t_test1(
  v_name varchar2(50),-- 姓名
  v_age number,-- 年龄
  q_1 number,-- 第一季度
  q_2 number,-- 第二季度
  q_3 number,-- 第三季度
  q_4 number -- 第四季度
);

-- 插入数据
insert all 
into t_test1
values ('张三',23,2000,3000,4000,5000)
into t_test1
values ('李四',34,3000,4000,5000,6000)
select 1 from dual;

-- 提交数据
commit;

行转列使用

-- 行转列-复杂表述
select t.* from (-- 加个括号是为了表示括号里是个整体,便于理解
  (
    select 
      v_name,
      v_age,
      q_1,
      q_2,
      q_3,
      q_4
    from t_test1
  )
  unpivot (
    v_value -- 值列
    for v_q in(-- 类型列
      q_1 as '第一季度',-- 加默认值
      q_2 as '第二季度',-- 加默认值
      q_3 as '第三季度',-- 加默认值
      q_4-- 不加默认值
    )
  )
) t;

-- 行转列-简单表述
select * from t_test1
unpivot (
  v_value -- 值列
  for v_q in(-- 类型列
    q_1 as '第一季度',-- 加默认值
    q_2 as '第二季度',-- 加默认值
    q_3 as '第三季度',-- 加默认值
    q_4-- 不加默认值
  )
);

ORACLE-列转行-pivot

数据准备

-- 此表用于表示某人四个季度的收入：用于列转行测试
create table t_test2(
  v_name varchar2(50),-- 姓名
  v_age number,-- 年龄
  v_q varchar2(10),-- 季节类型列
  v_value number-- 值列
);


-- 插入数据
insert all 
into t_test2
values ('张三',23,'第一季度',2000) 
into t_test2
values ('张三',23,'第二季度',3000) 
into t_test2
values ('张三',23,'第三季度',4000) 
into t_test2
values ('张三',23,'第四季度',5000) 
into t_test2
values ('李四',34,'第一季度',3000)
into t_test2
values ('李四',34,'第二季度',4000)
into t_test2
values ('李四',34,'第三季度',5000)
into t_test2
values ('李四',34,'第四季度',6000)
select 1 from dual;

-- 提交数据
commit;

列转行使用

-- 列转行-复杂表述
select t.* from(-- 加个括号是为了表示括号里是个整体,便于理解
  (
    select 
      v_name,v_age,-- 这两个字段用于分组，相当于group by,一般只取主键
      v_q,-- 这个字段用于区分列，用于生成不同列
      v_value -- 这个字段用于汇总统计
    from t_test2
  )
  pivot (
    sum(v_value)
    for v_q in (
      '第一季度' as 第一季度,
      '第二季度' q_2,
      '第三季度' as q_3,
      '第四季度'
    )
  )
) t;

-- 列转行-简单表述
select * from t_test2
pivot(
  sum(v_value) as 后缀
  for v_q in (
    '第一季度' as 第一季度,
    '第二季度' q_2,
    '第三季度' as q_3,
    '第四季度'
  )
);