- Pivot – Converting Rows Into Columns
- Unpivot – Converting Columns Into Rows
PIVOT
Multiple rows are aggregated and transposed into columns, with each column representing a different range of aggregate data.
Syntax
SELECT ...
FROM ...
PIVOT [XML]
( pivot_clause
pivot_for_clause
pivot_in_clause )
WHERE ...
- pivot_clause: defines the columns to be aggregated (pivot is an aggregate operation);
- pivot_for_clause: defines the columns to be grouped and pivoted;
- pivot_in_clause: defines the filter for the column(s) in the pivot_for_clause (i.e. the range of values to limit the results to). The aggregations for each value in the pivot_in_clause will be transposed into a separate column (where appropriate).
Examples
-- Simple Pivot
-- ==============
SELECT * FROM(SELECT department_id, TO_CHAR(hire_date, 'YYYY') AS YEAR FROM employees)
PIVOT (COUNT(*) FOR YEAR IN (2004, 2005))
ORDER BY department_id;
-- Alias for the aggregate function "EMPLOYEES_HIRED"
-- ===================================================
SELECT * FROM(SELECT department_id, TO_CHAR(hire_date,'YYYY') AS YEAR FROM employees)
PIVOT (COUNT(*) AS EMPLOYEES_HIRED FOR YEAR IN (2004, 2005))
ORDER BY department_id;
-- Alias For Aggregate function, Values Inside Parentheses
-- ===================================================
SELECT * FROM(SELECT department_id AS dept_id, TO_CHAR(hire_date,'YYYY') AS YEAR FROM employees)
PIVOT (COUNT(*) AS EMP_HIRED FOR YEAR IN (2004 AS YEAR2004 , 2005 AS YEAR2005))
ORDER BY department_id;
-- More than one aggregate function
-- ==================================
SELECT * FROM(SELECT department_id AS, TO_CHAR(hire_date, 'YYYY') AS YEAR, salary FROM employees)
PIVOT (COUNT( * ) AS employees_hired,
SUM(salary) AS salaries
FOR YEAR IN (2004 AS YEAR2004, 2005 AS YEAR2005))
ORDER BY department_id;
-- Pivot Data Using WITH
-- =======================
WITH pivot_data AS (SELECT department_id, job_id, salary FROM employees)
SELECT *
FROM pivot_data
PIVOT(SUM(salary)
FOR department_id
IN (10,20,30,40)
);
-- Pivot Data Using Pivot In Clause
-- =================================
SELECT *
FROM (SELECT department_id, job_id, salary FROM employees)
PIVOT (SUM(salary) --<-- pivot_clause
FOR department_id --<-- pivot_for_clause
IN (50,60,70,80,90,100) --<-- pivot_in_clause
)
WHERE job_id IN ('FI_ACCOUNT','SA_REP','ST_CLERK');
SELECT *
FROM(SELECT department_id, job_id, salary FROM employees)
PIVOT(SUM(salary)
FOR department_id
IN (10,20,30,40));
-- Alias All Elements Of Pivot Query
-- ====================================
SELECT *
FROM(SELECT employee_id, department_id, job_id, salary FROM employees)
PIVOT(SUM(salary) AS salary_total,
COUNT(employee_id) AS emp_cnt
FOR department_id IN (10 AS d10,
20 AS d20,
30 AS d30));
-- Doubled Number Of Pivot Columns(Doubled Number Of Aggregates)
-- ==================================
SELECT *
FROM(SELECT department_id, job_id, salary FROM employees)
PIVOT(SUM(salary) AS sum,
COUNT(salary) AS cnt
FOR(department_id,job_id) IN ((30, 'PU_MAN') AS d30_mgr,
(30, 'PU_CLERK') AS d30_clrk));
-- Referencing Pivot Column Directly
-- ==================================
SELECT d30_mgr_sum, d30_mgr_cnt
FROM(SELECT department_id, job_id, salary FROM employees)
PIVOT(SUM(salary) AS sum,
COUNT(salary) AS cnt
FOR(department_id,job_id) IN ((30, 'PU_MAN') AS d30_mgr,
(30, 'PU_CLERK') AS d30_clrk));
-- Pivot XML
-- ==========
SELECT *
FROM (SELECT department_id, job_id, salary FROM employees)
PIVOT XML(SUM(salary) AS salaries
FOR department_id
IN (SELECT department_id FROM departments WHERE department_id IN (10,20,30,40)));
SELECT job_id,
EXTRACT(department_id_xml, '/PivotSet/item/column') AS salary_elements
FROM (SELECT department_id, job_id, salary FROM employees)
PIVOT XML(SUM(salary) AS salaries
FOR department_id
IN (SELECT department_id FROM departments WHERE department_id IN (10,20,30,40)))
WHERE job_id = 'AD_ASST';
Outputs
-- Simple Pivot
-- ==================================
DEPARTMENT_ID 2004 2005
------------- ---------- ----------
10 0 0
20 1 1
30 0 2
40 0 0
50 4 12
60 0 1
70 0 0
80 5 10
90 0 1
100 0 2
110 0 0
0 0
-- Alias for the aggregate function "EMPLOYEES_HIRED"
-- ===================================================
DEPARTMENT_ID 2004_EMPLOYEES_HIRED 2005_EMPLOYEES_HIRED
------------- -------------------- --------------------
10 0 0
20 1 1
30 0 2
40 0 0
50 4 12
60 0 1
70 0 0
80 5 10
90 0 1
100 0 2
110 0 0
0 0
-- Alias For Aggregate function, Values Inside Parentheses
-- ========================================================
DEPT_ID YEAR2004_EMP_HIRED YEAR2005_EMP_HIRED
------- ------------------ ------------------
10 0 0
20 1 1
30 0 2
40 0 0
50 4 12
60 0 1
70 0 0
80 5 10
90 0 1
100 0 2
110 0 0
0 0
-- More Than One Aggregate Function
-- ==================================
-- Pivot Data Using WITH & Pivot Data Using Pivot In Clause(Eg:2)
-- =========================================================
JOB_ID 10 20 30 40
---------- ---------- ---------- ---------- ----------
IT_PROG
AC_MGR
AC_ACCOUNT
ST_MAN
PU_MAN 11000
AD_ASST 4400
AD_VP
SH_CLERK
FI_ACCOUNT
FI_MGR
PU_CLERK 13900
SA_MAN
MK_MAN 13000
PR_REP
AD_PRES
SA_REP
MK_REP 6000
ST_CLERK
HR_REP 6500
-- Pivot Data Using Pivot In Clause
-- =================================
-- Alias All Elements Of Pivot Query
-- ====================================
-- Doubled Number Of Pivot Columns(Doubled Number Of Aggregates)
-- ==================================
D30_MGR_SUM D30_MGR_CNT D30_CLRK_SUM D30_CLRK_CNT
----------- ----------- ------------ ------------
11000 1 13900 5
-- Referencing Pivot Column Directly
-- ==================================
D30_MGR_SUM D30_MGR_CNT
----------- -----------
11000 1UNPIVOT
An unpivot operation is the opposite of pivot (albeit without the ability to disaggregate the data). A simpler way of thinking about unpivot is that it turns pivoted columns into rows (one row of data for every column to be unpivoted).
Syntax
SELECT ...
FROM ...
UNPIVOT [INCLUDE|EXCLUDE NULLS]
( unpivot_clause
unpivot_for_clause
unpivot_in_clause )
WHERE ...
- unpivot_clause: this clause specifies a name for a column to represent the unpivoted measure values.
- unpivot_for_clause: the unpivot_for_clause specifies the name for the column that will result from our unpivot query. The data in this column describes the measure values in the unpivot_clause column.
- unpivot_in_clause: this contains the list of pivoted columns (not values) to be unpivoted.
Examples
-- Simple Unpivot
-- ===============
SELECT *
FROM locations
UNPIVOT(column_v --<-- unpivot_clause
FOR column_h --<-- unpivot_for_clause
IN (country_id, city) --<-- unpivot_in_clause
);
-- Unpivot Select Column
-- =======================
SELECT location_id,
column_h,
column_v
FROM locations
UNPIVOT (column_v
FOR column_h
IN (country_id, city)
);
SELECT employee_id AS emp_id,
first_name,
property,
property_val
FROM employees
UNPIVOT (property_val
FOR property
IN(salary,commission_pct))
WHERE department_id = 80;
-- Unpivot Exclude Nulls
-- ======================
SELECT employee_id AS emp_id,
first_name,
property,
property_val
FROM employees
UNPIVOT EXCLUDE NULLS(property_val
FOR property
IN(salary,commission_pct))
WHERE department_id = 30;
-- Unpivot Include Nulls
-- ======================
SELECT employee_id,
first_name,
property,
property_val
FROM employees
UNPIVOT INCLUDE NULLS(property_val
FOR property
IN(salary,commission_pct))
WHERE department_id = 30;
Output
-- Simple Unpivot
-- ===============
-- Unpivot Select Column
-- =======================
LOCATION_ID COLUMN_H COLUMN_V
----------- ---------- ------------------------------
1000 COUNTRY_ID IT
1000 CITY Roma
1100 COUNTRY_ID IT
1100 CITY Venice
1200 COUNTRY_ID JP
1200 CITY Tokyo
1300 COUNTRY_ID JP
1300 CITY Hiroshima
1400 COUNTRY_ID US
1400 CITY Southlake
1500 COUNTRY_ID US
1500 CITY South San Francisco
-- Unpivot Select Column (Eg:2)
-- =======================
EMPLOYEE_ID FIRST_NAME PROPERTY PROPERTY_VAL
----------- ------------ -------------- ------------
145 John SALARY 14000
145 John COMMISSION_PCT .4
146 Karen SALARY 13500
146 Karen COMMISSION_PCT .3
147 Alberto SALARY 12000
147 Alberto COMMISSION_PCT .3
148 Gerald SALARY 11000
148 Gerald COMMISSION_PCT .3
149 Eleni SALARY 10500
149 Eleni COMMISSION_PCT .2
150 Peter SALARY 10000
150 Peter COMMISSION_PCT .3
151 David SALARY 9500
151 David COMMISSION_PCT .25
152 Peter SALARY 9000
152 Peter COMMISSION_PCT .25
153 Christopher SALARY 8000
153 Christopher COMMISSION_PCT .2
154 Nanette SALARY 7500
154 Nanette COMMISSION_PCT .2
155 Oliver SALARY 7000
-- Unpivot Exclude Nulls
-- ======================
EMP_ID FIRST_NAME PROPERTY PROPERTY_VAL
------ ----------- --------- ------------
114 Den SALARY 11000
115 Alexander SALARY 3100
116 Shelli SALARY 2900
117 Sigal SALARY 2800
118 Guy SALARY 2600
119 Karen SALARY 2500
-- Unpivot Include Nulls
-- ======================
EMP_ID FIRST_NAME PROPERTY PROPERTY_VAL
------ ----------- -------------- ------------
114 Den SALARY 11000
114 Den COMMISSION_PCT
115 Alexander SALARY 3100
115 Alexander COMMISSION_PCT
116 Shelli SALARY 2900
116 Shelli COMMISSION_PCT
117 Sigal SALARY 2800
117 Sigal COMMISSION_PCT
118 Guy SALARY 2600
118 Guy COMMISSION_PCT
119 Karen SALARY 2500
119 Karen COMMISSION_PCTPivot Feature Using CASE and DECODE
Prior to 11g Pivot and Unpivot were not introduced. So in order to obtain this Pivot feature in 10g we can use CASE and DECODE.
Examples
-- CASE
-- =======
SELECT department_id, COUNT(
CASE TO_CHAR(hire_date, 'YYYY')
WHEN '2004'
THEN 1
END) AS "2004", COUNT(
CASE TO_CHAR(hire_date, 'YYYY')
WHEN '2005'
THEN 1
END) AS "2005"
FROM employees
GROUP BY department_id
ORDER BY department_id;
-- DECODE
-- =======
SELECT job_id
SUM(DECODE(department_id,10,salary)) AS "D10_SAL",
SUM(DECODE(department_id,20,salary)) AS "D20_SAL",
SUM(DECODE(department_id,30,salary)) AS "D30_SAL",
SUM(DECODE(department_id,40,salary)) AS "D40_SAL"
FROM employees
GROUP BY job_id;
