SQL: Unpivoting Data for Analysis

sql-unpivot
Spread the love

Introduction

In SQL , learning unpivoting of data is as important as learning pivoting of data.
Sometimes we need to transform data from column to rows for efficient analysis.

The UNPIVOT function is useful when you have data in a pivoted format, with values spread across multiple columns, and you want to convert it back into a more normalized tabular format

Syntax:

SELECT 
    <non-unpivoted column>,
    <unpivoted column>,
    <value column>
FROM 
    (SELECT <columns> FROM <source_table>) AS <alias>
UNPIVOT 
(
    <value column> 
    FOR <unpivoted column> IN ([column1], [column2], ...)
) AS <unpivot_alias>;

<non-unpivoted column> are columns that we don’t want to change like id, etc.
<unpivoted column> are the new columns that will contain the values from the original pivoted columns.
<value column> is column that will contain the actual values from the original pivoted columns.

Example:

To demonstrate the use of Unpivot, we are going to use the table ‘MARKS’

SELECT *
FROM MARKS;

Lets UNPIVOT the above table

SELECT
    STUDENT_ID,
    SUBJECT,
    Value
FROM
    (SELECT * FROM MARKS) AS SourceTable
UNPIVOT
(
    Value
    FOR SUBJECT IN ([MATHS], [SCIENCE], [ENGLISH])
) AS UnpivotedTable;

value columns displays the marks

If you are still confuse read about SQL Pivot

Source : Microsoft T-SQL Docs