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
