In database development, we often encounter row and column to row situations, there are many ways to achieve this function, such as using CASE...WHEN..., DECODE, or PIVOT and UNPIVOT. This article explains the use of PIVOT and UNPIVOT, and hope that readers can learn the use of row to column conversion.
PIVOT Syntax
SELECT ...
FROM ...
PIVOT [XML]
( pivot_clause
pivot_for_clause
pivot_in_clause )
WHERE ...
Explain:
pivot_clause: Defines the columns that will be aggregated;
pivot_for_clause: Defines the columns to be grouped and transformed;
Pivot_in_clause: Defines the filtering of columns in pivot_for_clause, and converts each value in pivot_in_clause to a single column.
pivot_clause::=
pivot_for_clause::=
pivot_in_clause::=
Demo (using Oracle's sample database with Schema as SCOTT):
1. First we produce some basic data for the basis of the presentation;
SQL> select job,deptno ,sum(sal) as sum_sal 2 from emp 3 group by job,deptno 4 order by job,deptno; JOB DEPTNO SUM_SAL --------- ---------- ---------- ANALYST 20 6000 CLERK 10 1300 CLERK 20 1900 CLERK 30 950 MANAGER 10 2450 MANAGER 20 2975 MANAGER 30 2850 PRESIDENT 10 5000 SALESMAN 30 6100 9 rows selected.
2. DEPTNO is transposed by PIVOT using WITH..as;
SQL> with pivot_emp as(
2 select job,deptno,sal from emp)
3 select *
4 from pivot_emp
5 pivot(
6 sum(sal)
7 for deptno
in(10 as dept_10,20 as dept_20,30 as dept_30,40 as dept_40)
8 );
JOB DEPT_10 DEPT_20 DEPT_30 DEPT_40
--------- ---------- ---------- ---------- ----------
CLERK 1300 1900 950
SALESMAN 6100
PRESIDENT 5000
MANAGER 2450 2975 2850
ANALYST 60003. You can also use the inline view to column DEPTNO using PIVOT as above.
SQL> select *from (select sal,deptno,job from emp) 2 pivot ( 3 sum(sal) 4 for deptno in(10,20,30,40) 5 ); JOB 10 20 30 40 --------- ---------- ---------- ---------- ---------- CLERK 1300 1900 950 SALESMAN 6100 PRESIDENT 5000 MANAGER 2450 2975 2850 ANALYST 6000 SQL>
4. Query the whole table to convert DEPTNO;
SQL> select *from emp 2 pivot( 3 sum(sal) for deptno in (10,20,30,40) 4 ); Result omitted...
5. Use of aliases in PIVOT queries;
SQL> select * 2 from (select job , deptno , sal from emp) 3 pivot( 4 sum(sal) as sum_sal 5 for deptno 6 in (10 as dept_10 , 20 , 30 , 40 as dept_40) 7 ); 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
Description: Aggregation functions in pivot_clause splice column names or aliases in pivot_in_clause if aliases are set. If not, only column names or aliases in pivot_in_clause are displayed.
6. PIVOT multi-line query;
SQL> select * 2 from (select job , deptno , sal from emp) 3 pivot( 4 sum(sal) as sum_sal, 5 count(sal) as cnt 6 for deptno 7 in (10 as dept_10, 20 ,30 ,40) 8 ); 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 queries with multiple columns;
SQL> select * 2 from (select job ,deptno, sal from emp) 3 pivot( 4 sum(sal) as sum_sal,count(1) as cnt 5 for (deptno,job) 6 in( 7 (30,'MANAGER') as d30_mgr, 8 (30,'CLERK') as d30_clerk, 9 (30,'SALESMAN') as d30_salman 10 ) 11 ); 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. Knowing the naming rules for columns, you can list the columns you want to query in the Select field.
SQL> select d30_mgr_sum_sal, d30_mgr_cnt 2 from (select job ,deptno, sal from emp) 3 pivot( 4 sum(sal) as sum_sal,count(1) as cnt 5 for (deptno,job) 6 in( 7 (30,'MANAGER') as d30_mgr, 8 (30,'CLERK') as d30_clerk, 9 (30,'SALESMAN') as d30_salman 10 ) 11 ); D30_MGR_SUM_SAL D30_MGR_CNT --------------- ----------- 2850 1
9. View the execution plan of PIVOT conversion, in fact, it is only a rewrite of CASE...WHEN;
SCOTT@orcl _SQL>explain plan set statement_id='pivot'
2 for
3 select *
4 from (select job,deptno,sal from emp)
5 pivot(
6 sum(sal)
7 for deptno
8 in (10 as dept_10,20 as dept_20,30 as dept_30,40 as dept_40)
9 );
Explained.
SCOTT@orcl _SQL>select *from table(dbms_xplan.display(null,'pivot','TYPICAL +PROJECTION'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1475541029
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 75 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY PIVOT| | 5 | 75 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Column Projection Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------
1 - (#keys=1) "JOB"[VARCHAR2,9], SUM(CASE WHEN ("DEPTNO"=10) THEN
"SAL" END )[22], SUM(CASE WHEN ("DEPTNO"=20) THEN "SAL" END )[22],
SUM(CASE WHEN ("DEPTNO"=30) THEN "SAL" END )[22], SUM(CASE WHEN
("DEPTNO"=40) THEN "SAL" END )[22]
2 - "JOB"[VARCHAR2,9], "SAL"[NUMBER,22], "DEPTNO"[NUMBER,22]
18 rows selected.UNPIVOT Syntax
SELECT ...
FROM ...
UNPIVOT [INCLUDE|EXCLUDE NULLS]
( unpivot_clause
unpivot_for_clause
unpivot_in_clause )
WHERE ...
unpivot_clause::=
unpivot_in_clause::=
Demonstration:
1. Create a view to store the basic data;
SCOTT@orcl _SQL>create view pivoted_emp as 2 select * 3 from (select job,deptno,sal from emp) 4 pivot ( 5 sum(sal) 6 for deptno 7 in (10 as dept10,20 as dept20,30 as dept30,40 as dept40) 8 ); SCOTT@orcl _SQL>select *from pivoted_emp; JOB DEPT10 DEPT20 DEPT30 DEPT40 --------- ---------- ---------- ---------- ---------- CLERK 1300 1900 950 SALESMAN 6100 PRESIDENT 5000 MANAGER 2450 2975 2850 ANALYST 6000 SCOTT@orcl _SQL>
2. Use UNPIVOT for column-to-row operation;
SCOTT@orcl _SQL>select * 2 from pivoted_emp 3 unpivot ( 4 sal 5 for deptno in (dept10 as 10,dept20 as 20,dept30 as 30, dept40 as 40) 6 ); 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 9 rows selected. SCOTT@orcl _SQL>
3. From the results above, JOB s that do not contain NULL values need to use the INCLUDE keyword if they are displayed.
SCOTT@orcl _SQL>select * 2 from pivoted_emp 3 unpivot include nulls( 4 sal 5 for deptno in (dept10 as 10,dept20 as 20,dept30 as 30, dept40 as 40) 6 ); 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 ANALYST 40