Oracle PIVOT (二)实列

 

--使用格式：

select t2.* from table1 as t1
pivot ([聚合函数] for [行转列的条件列] in ([与“行转列条件列”匹配]) t2;

/*
聚合函数（sum，max，avg等），可以支持一或多个，但是需要使用别名（列：sum(t1.x1) a,sum(t1.x2) b)
行转列的条件列，如果是需要多个列，使用concat或者||拼接成一个列
*/

 

with tab1 as (
select 1 BH, '单选' C_TX, 1   N_FS , 5 N_TS from dual union all
select 1 BH, '多选' C_TX, 1   N_FS , 2 N_TS from dual union all
select 2 BH, '单选' C_TX, 0.5 N_FS , 4 N_FS from dual union all
select 2 BH, '多选' C_TX, 1   N_FS , 3 N_FS from dual 
)
select *
  from (select BH, C_TX,N_FS,N_TS
  from tab1)pivot(MAX(N_FS) 每题分数,MAX(N_TS) 分配题数 for C_TX in ('单选' as 单选,
                              '多选' AS 多选,
                              '判断' AS 判断))
;

/*
--结果
BH	单选_每题分数	单选_分配题数	多选_每题分数	多选_分配题数	判断_每题分数	判断_分配题数
1	1	            5	            1	            2		
2	0.5	            4	            1	            3
*/		

实例：

/*源数据*/
SELECT *
  FROM (SELECT A1.C_ZJGZMXJLBH, A1.N_BZFZ, A2.C_TX, A2.N_FS, A2.N_TS
          FROM T_KT_ZJGZ_MX A1, T_KT_ZJGZ_TX A2
         WHERE A1.C_ZJGZMXJLBH = A2.C_ZJGZMXJLBH(+)) T1 PIVOT(SUM(N_FS) FS, SUM(N_TS) TS FOR C_TX IN ('1' AS N_STLX_1, '2' AS N_STLX_2, '3' AS N_STLX_3, '4' AS N_STLX_4)) ;


/*
C_ZJGZMXJLBH	N_BZFZ	N_FZHJ	N_STLX_1_FS	N_STLX_1_TS	N_STLX_2_FS	N_STLX_2_TS	N_STLX_3_FS	N_STLX_3_TS	N_STLX_4_FS	N_STLX_4_TS
3		8.00	0								
1		10.00	7	1		5		1		2				
2		5.00	5	0.5		4		1		3				
*/
--方式一
SELECT T1.C_ZJGZMXJLBH ,T1.N_BZFZ,
       NVL(T1.N_STLX_1_FS * T1.N_STLX_1_TS,0) +
       NVL(T1.N_STLX_2_FS * T1.N_STLX_2_TS,0) +
       NVL(T1.N_STLX_3_FS * T1.N_STLX_3_TS,0) +
       NVL(T1.N_STLX_4_FS * T1.N_STLX_4_TS,0)  N_FZHJ,
       T1.N_STLX_1_FS, T1.N_STLX_1_TS ,
       T1.N_STLX_2_FS, T1.N_STLX_2_TS ,
       T1.N_STLX_3_FS, T1.N_STLX_3_TS ,
       T1.N_STLX_4_FS, T1.N_STLX_4_TS 
  FROM(
  SELECT *
  FROM (SELECT A1.C_ZJGZMXJLBH, A1.N_BZFZ, A2.C_TX, A2.N_FS, A2.N_TS
          FROM T_KT_ZJGZ_MX A1, T_KT_ZJGZ_TX A2
         WHERE A1.C_ZJGZMXJLBH = A2.C_ZJGZMXJLBH(+)) PIVOT(SUM(N_FS) FS, SUM(N_TS) TS FOR C_TX IN ('1' AS N_STLX_1, '2' AS N_STLX_2, '3' AS N_STLX_3, '4' AS N_STLX_4)) 
  ) T1;
/*
C_ZJGZMXJLBH	N_FZHJ	N_STLX_1_FS	N_STLX_1_TS	N_STLX_2_FS	N_STLX_2_TS	N_STLX_3_FS	N_STLX_3_TS	N_STLX_4_FS	N_STLX_4_TS
1		7		1	5		1		2		0	0	0	0
3		0		0	0		0		0		0	0	0	0
2		5		0.5	4		1		3		0	0	0	0
*/
--方式二 
SELECT T1.C_ZJGZMXJLBH ,SUM(NVL(T1.N_FZHJ,0)) N_FZHJ,
       SUM(NVL(T1.N_STLX_1_FS,0)) N_STLX_1_FS, SUM(NVL(T1.N_STLX_1_TS,0)) N_STLX_1_TS,
       SUM(NVL(T1.N_STLX_2_FS,0)) N_STLX_2_FS, SUM(NVL(T1.N_STLX_2_TS,0)) N_STLX_2_TS,
       SUM(NVL(T1.N_STLX_3_FS,0)) N_STLX_3_FS, SUM(NVL(T1.N_STLX_3_TS,0)) N_STLX_3_TS,
       SUM(NVL(T1.N_STLX_4_FS,0)) N_STLX_4_FS, SUM(NVL(T1.N_STLX_4_TS,0)) N_STLX_4_TS
  FROM(
  SELECT *
    FROM (SELECT A1.C_ZJGZMXJLBH,  A2.C_TX, A2.N_FS, A2.N_TS,A2.N_FS* A2.N_TS N_FZHJ
            FROM T_KT_ZJGZ_MX A1, T_KT_ZJGZ_TX A2
           WHERE A1.C_ZJGZMXJLBH = A2.C_ZJGZMXJLBH(+)) T1 PIVOT(SUM(N_FS) FS, SUM(N_TS) TS FOR C_TX IN ('1' AS N_STLX_1, '2' AS N_STLX_2, '3' AS N_STLX_3, '4' AS N_STLX_4))
  ) T1
  group by T1.C_ZJGZMXJLBH;

 

 

 

参考 https://blog.csdn.net/paopaopotter/article/details/81735922

参考：https://www.cnblogs.com/xiao02fang/p/9705609.html

工作中，我们经常会碰到行转列的情况

这里我介绍几种简单的方法--行转列

1.oracle的pivot函数

原表

使用pivot函数：

with temp as(
select '四川省' nation ,'成都市' city,'第一' ranking from dual union all 
select '四川省' nation ,'绵阳市' city,'第二' ranking from dual union all 
select '四川省' nation ,'德阳市' city,'第三' ranking from dual union all 
select '四川省' nation ,'宜宾市' city,'第四' ranking from dual union all 
select '湖北省' nation ,'武汉市' city,'第一' ranking from dual union all 
select '湖北省' nation ,'宜昌市' city,'第二' ranking from dual union all 
select '湖北省' nation ,'襄阳市' city,'第三' ranking from dual 
)
select * from （select nation,city,ranking from temp）pivot (max(city) for ranking in ('第一' as 第一,'第二' AS 第二,'第三' AS 第三,'第四' AS 第四));

说明：pivot（聚合函数 for 列名 in（类型）），其中 in(‘’) 中可以指定别名，in中还可以指定子查询，比如 selectdistinct ranking from temp

 

SELECT * FROM [StudentScores] /*数据源*/
AS P
PIVOT 
(
    SUM(Score/*行转列后 列的值*/) FOR 
    p.Subject/*需要行转列的列*/ IN ([语文],[数学],[英语],[生物]/*列的值*/)
) AS T

2.使用max结合decode函数

原表

使用max结合decode函数

with temp as(
select '四川省' nation ,'成都市' city,'第一' ranking from dual union all 
select '四川省' nation ,'绵阳市' city,'第二' ranking from dual union all 
select '四川省' nation ,'德阳市' city,'第三' ranking from dual union all 
select '四川省' nation ,'宜宾市' city,'第四' ranking from dual union all 
select '湖北省' nation ,'武汉市' city,'第一' ranking from dual union all 
select '湖北省' nation ,'宜昌市' city,'第二' ranking from dual union all 
select '湖北省' nation ,'襄阳市' city,'第三' ranking from dual 
)
select nation,
max(decode(ranking, '第一', city, '')) as 第一,
max(decode(ranking, '第二', city, '')) as 第二,
max(decode(ranking, '第三', city, '')) as 第三,
max(decode(ranking, '第四', city, '')) as 第四
from temp group by nation;

说明：decode的用法：decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

该函数的含义如下：

IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

3.使用max结合case when 函数

原表

使用max结合case when 函数

select case when grade_id='1' then '一年级'

when grade_id='2' then '二年级'

when grade_id='5' then '五年级' 

else null end "年级",

max(case when subject_name='语文'  then max_score

else 0 end) "语文" ,

max(case when subject_name='数学'  then max_score

else 0 end) "数学" ,

max(case when subject_name='政治'  then max_score

else 0 end) "政治"

from dim_ia_test_ysf

group by

case when grade_id='1' then '一年级'

when grade_id='2' then '二年级'

when grade_id='5' then '五年级' 

else null end

 

 

 

下面是介绍列转行方法--列转行

原表

with temp as(
select '四川省' nation ,'成都市' 第一,'绵阳市' 第二,'德阳市' 第三,'宜宾市' 第四  from dual union all 
select '湖北省' nation ,'武汉市' 第一,'宜昌市' 第二,'襄阳市' 第三,'' 第四   from dual 
)
select nation,name,title from 
temp
unpivot
(name for title in (第一,第二,第三,第四))t

 说明：unpivot（自定义列名/*列的值*/ for 自定义列名/*列名*/ in（列名））