- 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 1
UNPIVOT
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_PCT
Pivot 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;