UNPIVOT Oracle 11g feature

Learn how to transpose columns to rows using oracle UNPIVOT clasuse

UNPIVOT clause allows transpose columns to rows.

UNPIVOT is purely opposite to PIVOT clause Except UNPIVOT cannot perform aggregate operations.

Syntax

SELECT 
    select_column_list
FROM table_name
UNPIVOT [INCLUDE | EXCLUDE NULLS](
    unpivot_clause
    unpivot_for_clause
    unpivot_in_clause 
);

The unpivot_clause allows you to specify a name for a column that represents the unpivoted measure values.
The unpivot_for_clause allows you to specify the name for each column that will hold the measure’s values.
The unpivot_in_clause contains the pivoted columns that will be unpivoted.

The INCLUDE | EXCLUDE NULLS clause allows you to include or exclude null-valued rows.

The INCLUDE NULLS clause instructs Oracle to include null-valued rows.
The EXCLUDE NULLS clause, on the other hand, eliminates null-valued rows from the returned result set.

By default, the unpivot operation excludes null-valued rows.

Let’s take some examples of using the Oracle UNPIVOT clause to get a better understanding.

Let’s create a sample table name called STUDENT_MARKS for demonstration

create table STUDENT_MARKS
(
  STUDENT_ID   NUMBER,
  STUDENT_NAME VARCHAR2(100),
  TELUGU       NUMBER(3),
  HINDI        NUMBER(3),
  ENGLISH      NUMBER(3),
  MATHS        NUMBER(3),
  PHYS         NUMBER(3),
  CHEM         NUMBER(3),
  MARKS        NUMBER default "TELUGU"+"HINDI"+"ENGLISH"+"MATHS"+"PHYS"+"CHEM",
  PERCENTAGE   NUMBER default ("TELUGU"+"HINDI"+"ENGLISH"+"MATHS"+"PHYS"+"CHEM")*100/600
);

NOTES:

In the above table creation script, MARKS and PERCENTAGE columns are virtual columns that calculates the value based on the expression at run time

insert some rows into the STUDENT_MARKS

insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)
values (1, 'VISWANATH', 87, 84, 74, 97, 74, 74);

insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)
values (2, 'VINITH', 88, 83, 79, 100, 79, 89);

insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)
values (3, 'RAVI', 87, 52, 59, 100, 98, 68);

insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)
values (4, 'RAGHU', 88, 79, 85, 89, 69, 96);

select the data from STUDENT_MARKS table using below query.

SELECT *
FROM STUDENT_MARKS;

Output

Unpivot Example Query

This statement uses the UNPIVOT clause to rotate columns TELUGU, HINDI, ENGLISH, MATHS, PHYS and CHEM into rows.

SELECT STUDENT_ID,
         STUDENT_NAME,
         SUBJECT_NAME, 
         SUB_MARKS
  FROM   STUDENT_MARKS 
  UNPIVOT
         (SUB_MARKS -- unpivot_clause
                    FOR SUBJECT_NAME --  unpivot_for_clause
                    IN( -- unpivot_in_clause
                         TELUGU AS 'TELUGU',
                         HINDI AS 'HINDI',
                         ENGLISH AS 'ENGLISH',
                         MATHS AS 'MATHS',
                         PHYS AS 'PHYSICS',
                         CHEM AS 'CHEMISTRY'
                       )
          );

Output

The unpivot_clause is SUB_MARKS which is a column that represents the unpivoted values from the TELUGU, HINDI, ENGLISH, MATHS, PHYS and CHEM columns.

The unpivot_for_clause is FOR SUBJECT_NAME , which is the column that will hold the measure’s values.

The unpivot_in_clause clause is:

                    IN( -- unpivot_in_clause
                         TELUGU AS 'TELUGU',
                         HINDI AS 'HINDI',
                         ENGLISH AS 'ENGLISH',
                         MATHS AS 'MATHS',
                         PHYS AS 'PHYSICS',
                         CHEM AS 'CHEMISTRY'
                       )

which instructs Oracle to unpivot values in the TELUGU, HINDI, ENGLISH, MATHS, PHYS and CHEM columns.

Query Example to find the names who scored highest marks as per subject wise.

WITH CTE AS
(
  SELECT STUDENT_ID,
         STUDENT_NAME,
         SUBJECT_NAME, 
         SUB_MARKS
  FROM   STUDENT_MARKS 
  UNPIVOT
         (SUB_MARKS -- unpivot_clause
                    FOR SUBJECT_NAME --  unpivot_for_clause
                    IN( -- unpivot_in_clause
                         TELUGU AS 'TELUGU',
                         HINDI AS 'HINDI',
                         ENGLISH AS 'ENGLISH',
                         MATHS AS 'MATHS',
                         PHYS AS 'PHYSICS',
                         CHEM AS 'CHEMISTRY'
                       )
          )

)
SELECT   X.STUDENT_ID,
         X.STUDENT_NAME,
         X.SUBJECT_NAME, 
         X.SUB_MARKS
FROM CTE X,
(          
   SELECT SUBJECT_NAME, 
          MAX(SUB_MARKS) SUB_MARKS 
   FROM CTE  
   GROUP BY SUBJECT_NAME
 ) Y
WHERE X.SUBJECT_NAME= Y.SUBJECT_NAME
AND X.SUB_MARKS=Y.SUB_MARKS
ORDER BY   X.SUBJECT_NAME;