In SQL, Pivot and Unpivot serve as relational operators employed to reshape one table into another, aiming to create a more streamlined table view. In conventional terms, the Pivot operator restructures the data from rows in the table into columns, whereas the Unpivot operator performs the inverse action, transitioning column-based data into rows.
SQL PIVOT diagram
Imagine we have a table name called School below. The pictures below demonstrates the capabilities of PIVOT, as it transform three rows of data (shades of red) into a single row with two distinct columns.
Syntax:
SELECT *
FROM (School)
PIVOT (MIN(Grade) FOR
Subject IN (Bio, Chem) )
SQL UNPIVOT diagram
Conversely, unpivot reverses this process by converting multiple columns (shades of red) into multiple rows.
Syntax:
SELECT *
FROM (School)
UNPIVOT (Grade
FOR Subject IN (Bio, Chem) )
Note that we need to manually type all of the subject name inside IN (), which is not as handy as Tableaus Prep and Alteryx Designer.