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.
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
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.
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