Oracle 11g的Pivot和Unpivot函数简单测试

Oracle对行列转换提供了Pivot和Unpivot两个函数,下面是个简单的测试

一、创建表和测试数据

create table T_SCH_SCORE(  STU_NAME   VARCHAR2(20),  CLASS_TYPE VARCHAR2(10),  SCORE      NUMBER)
insert into t_sch_score (STU_NAME, CLASS_TYPE, SCORE, ROWID)values ('张三', '01', 100, '');
insert into t_sch_score (STU_NAME, CLASS_TYPE, SCORE, ROWID)values ('张三', '02', 98, '');
insert into t_sch_score (STU_NAME, CLASS_TYPE, SCORE, ROWID)values ('张三', '03', 86, '');
insert into t_sch_score (STU_NAME, CLASS_TYPE, SCORE, ROWID)values ('李四', '01', 99, '');
insert into t_sch_score (STU_NAME, CLASS_TYPE, SCORE, ROWID)values ('李四', '02', 85, '');
insert into t_sch_score (STU_NAME, CLASS_TYPE, SCORE, ROWID)values ('李四', '03', 90, '');
insert into t_sch_score (STU_NAME, CLASS_TYPE, SCORE, ROWID)values ('王五', '01', 88, '');

二、行转列的Pivot函数

select * from (
    select  * from t_sch_score
) pivot (
    sum(score) as score for class_type in ('01' as "语文" , '02' as "数学", '03' as "英语")
)

运行结果:

STU_NAME 语文_SCORE 数学_SCORE 英语_SCORE
张三 100 98 86
王五 88
李四 99 85 90

三、列转行Unpivot函数

select * from
(
  select * from (
      select  * from t_sch_score
  ) pivot (
      sum(score) as score for class_type in ('01' as "语文" , '02' as "数学", '03' as "英语")
  )
)
unpivot (
  score for class_type in ("语文_SCORE" , "数学_SCORE", "英语_SCORE")
)

运行结果:

STU_NAME CLASS_TYPE SCORE
张三 语文_SCORE 100
张三 数学_SCORE 98
张三 英语_SCORE 86
王五 语文_SCORE 88
李四 语文_SCORE 99
李四 数学_SCORE 85
李四 英语_SCORE 90

四、注意事项
行转列中,为转置的列起别名的时候,名称必须在双引号中;如果不写别名,生成列的名字是带单引号的。
同样,在列转行IN语句中的字段名称,也必须在双引号中。

Comments are closed.