oracle -pivot和unpivot进行行列转换

最新推荐文章于 2024-07-18 16:16:20 发布

刘_vera

最新推荐文章于 2024-07-18 16:16:20 发布

阅读量809

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/qq_41584921/article/details/88822122

Oracle 专栏收录该内容

32 篇文章

订阅专栏

本文介绍了Oracle数据库中用于行列转换的PIVOT和UNPIVOT操作。通过实例展示了如何使用PIVOT将行转换为列，包括设置别名、多行查询和处理多列的情况。同时，也详细解释了UNPIVOT操作，如何将列转换回行，包括包含NULL值的情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

A表：

ID DTIME NUM
--------- -------- ---
101 20190201 10
101 20190202 20
101 20190203 50
101 20190203 20
102 20190201 20
102 20190202 30
102 20190203 60
103 20190201 60
103 20190203 90

select * from test_a pivot (
    sum(num) for dtime in (20190201,20190202,20190203)
);

        ID   20190201   20190202   20190203
---------- ---------- ---------- ----------
       101         10         20         70
       102         20         30         60
       103         60                    90

PIVOT语法

SELECT ...
   FROM ...
   PIVOT [XML]
            ( pivot_clause
               pivot_for_clause
               pivot_in_clause )
WHERE ...
说明：
pivot_clause：定义将要进行聚合操作的列；
pivot_for_clause：定义将要进行分组和转换的列；
pivot_in_clause：定义在pivot_for_clause中对列的过滤，对于pivot_in_clause中每一个值进行，并转换为单个列。

Sselect *from (select sal,deptno,job from emp)
pivot (
sum(sal)
   for deptno in(10,20,30,40)
   );

JOB       10   20    30   40
--------- ---------- ---------- ---------- ----------
CLERK       1300   1900 950
SALESMAN           6100
PRESIDENT   5000
MANAGER    2450   2975 2850
ANALYST        6000

4、查询整个表，对DEPTNO进行转换；
select *from emp
pivot(
sum(sal) for deptno in (10,20,30,40)
);

5、PIVOT查询对于别名的使用；
select * from (select job , deptno , sal from emp)
pivot(
sum(sal) as sum_sal
for deptno  in (10 as dept_10 , 20 , 30 , 40 as dept_40)
);
JOB   DEPT_10_SUM_SAL 20_SUM_SAL 30_SUM_SAL DEPT_40_SUM_SAL
--------- --------------- ---------- ---------- ---------------
CLERK       1300   1900   950
SALESMAN               6100
PRESIDENT   5000
MANAGER    2450   2975   2850
ANALYST            6000
说明：pivot_clause中的聚合函数，如果设置了别名，会和pivot_in_clause中的列名或别名进行拼接，如果不设置，则只显示pivot_in_clause中的列名或别名。
6、PIVOT多行查询；
select *
from (select job , deptno , sal from emp)
pivot(
sum(sal) as sum_sal,
count(sal) as cnt
for deptno
in (10 as dept_10, 20 ,30 ,40)
);
JOB   DEPT_10_SUM_SAL DEPT_10_CNT 20_SUM_SAL 20_CNT 30_SUM_SAL   30_CNT 40_SUM_SAL   40_CNT
--------- --------------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
CLERK       1300   1   1900   2   950       1       0
SALESMAN           0           0   6100       4       0
PRESIDENT   5000   1           0           0       0
MANAGER    2450   1   2975   1   2850       1       0
ANALYST            0   6000   2           0       0

7、for in含有多列的查询；
select *
from (select job ,deptno, sal from emp)
pivot(
sum(sal) as sum_sal,count(1) as cnt
for (deptno,job)
in(
(30,'MANAGER') as d30_mgr,
(30,'CLERK') as d30_clerk,
(30,'SALESMAN') as d30_salman
)
);

D30_MGR_SUM_SAL D30_MGR_CNT D30_CLERK_SUM_SAL D30_CLERK_CNT D30_SALMAN_SUM_SAL D30_SALMAN_CNT
--------------- ----------- ----------------- ------------- ------------------ --------------
2850 1 950 1 6100 4
8、知道列的命名规则，可以在Select字段中列出要查询的列；
select d30_mgr_sum_sal, d30_mgr_cnt
from (select job ,deptno, sal from emp)
pivot(
sum(sal) as sum_sal,count(1) as cnt
for (deptno,job)
in(
(30,'MANAGER') as d30_mgr,
(30,'CLERK') as d30_clerk,
(30,'SALESMAN') as d30_salman
)
);

D30_MGR_SUM_SAL D30_MGR_CNT
--------------- -----------
2850 1

UNPIVOT语法

SELECT ...
   FROM ...
UNPIVOT [INCLUDE|EXCLUDE NULLS]
       ( unpivot_clause
         unpivot_for_clause
         unpivot_in_clause )
WHERE ...

unpivot_clause::=

unpivot_in_clause::=

演示：
1、创建一个view，用于存放基础数据；
create view pivoted_emp as
select * from (select job,deptno,sal from emp)
pivot (
sum(sal) for deptno in (10 as dept10,20 as dept20,30 as dept30,40 as dept40)
);

JOB   DEPT10   DEPT20 DEPT30 DEPT40
--------- ---------- ---------- ---------- ----------
CLERK       1300   1900 950
SALESMAN           6100
PRESIDENT   5000
MANAGER    2450   2975 2850
ANALYST        6000

select *  from pivoted_emp
unpivot (
sal   for deptno in (dept10 as 10,dept20 as 20,dept30 as 30, dept40 as 40)
);

JOB   DEPTNO   SAL
--------- ---------- ----------
CLERK       10   1300
CLERK       20   1900
CLERK       30   950
SALESMAN   30   6100
PRESIDENT   10   5000
MANAGER    10   2450
MANAGER    20   2975
MANAGER    30   2850
ANALYST    20   6000

select * from pivoted_emp
unpivot include nulls(
sal
   for deptno in (dept10 as 10,dept20 as 20,dept30 as 30, dept40 as 40)
  );

JOB   DEPTNO   SAL
--------- ---------- ----------
CLERK       10   1300
CLERK       20   1900
CLERK       30   950
CLERK       40
SALESMAN   10
SALESMAN   20
SALESMAN   30   6100
SALESMAN   40
PRESIDENT   10   5000
PRESIDENT   20
PRESIDENT   30

JOB   DEPTNO   SAL
--------- ---------- ----------
PRESIDENT   40
MANAGER    10   2450
MANAGER    20   2975
MANAGER    30   2850
MANAGER    40
ANALYST    10
ANALYST    20   6000
ANALYST    30