Oracle – PIVOT & UNPIVOT

  • 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
-- ==================================
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
-- =================================
Pivot Data Using Pivot In Clause
-- Alias All Elements Of Pivot Query
-- ====================================
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
-- 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;

References

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s