PIVOT and UNPIVOT are relational operators. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. And PIVOT runs aggregations where they’re required on any remaining column values that are wanted in the final output. UNPIVOT carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

Syntax-

SELECT <non-pivoted column>, [first pivoted column] AS <column name>,…..[last pivoted column] AS <column name> 

FROM (<SELECT query that produces the data>) AS <alias for the source query> 

PIVOT ( <aggregation function>(<column being aggregated>) 

FOR [<column that contains the values that will become column headers>]  

IN ([first pivoted column], … [last pivoted column]) ) AS <alias for the pivot table> 

<optional ORDER BY clause>;

Example-

Table- SalesTable

Product	Year	Sale
Pen	2020	1000
Pen	2021	2000
Pencil	2020	5000
Pencil	2021	1000
Pen	2020	2000

Result Set- PivotTable

Year	Pen	Pencil
2020	3000	5000
2021	2000	1000

SELECT Year, Pen, Pencil

FROM (SELECT Product, Year, Sale FROM SalesTable) AS Table1   

PIVOT (SUM(Sale)

FOR Product

IN (Pen, Pencil)) AS PivotTable   

ORDER BY PivotTable.Year

Dynamic PIVOT table query-

CREATE PROCEDURE DynamicPivot 

  @PivotColumn NVARCHAR(255), 

  @PivotList NVARCHAR(255) 

AS 

BEGIN 

 DECLARE @Query NVARCHAR(MAX);   

 SET @PivotQuery = N’ 

    SELECT * FROM (SELECT [Product], [Year], [Sale] FROM SalesTable) AS Table1 

    PIVOT (SUM([Sale]) FOR [‘+@PivotColumn+’] IN (‘+@PivotList+’)) AS PivotTable’; 

  EXEC(@PivotQuery) 

END

Executing the stored procedure-

EXEC DynamicPivot  N’Product’, N'[Pen], [Pencil]’ 

UNPIVOT Operator-

UNPIVOT carries out almost the reverse operation of PIVOT, by rotating columns into rows.

SELECT (column_names)  

FROM (table_name)  

UNPIVOT 

 (  

   Aggregate_function (column to be aggregated) 

   FOR PivotColumn IN (pivot column values) 

 ) AS (alias_name)

Example-

Declares a temporary table variable @Tab:

DECLARE @Tab TABLE   

(   

   Year int,   

   Pen varchar(45), 

   Pencil varchar(45) 

) 

Insert values into this table as below:

INSERT INTO @Tab   

SELECT Year, Pen, Pencil FROM    

(SELECT Product, Year, Sale FROM SalesTable ) AS Table1   

PIVOT   

(SUM(Sale) FOR Product IN (Pen, Pencil)) AS PivotTable   

ORDER BY PivotTable.Year ;

Perform UNPIVOT operation:

SELECT Product, Year, Sale FROM @Tab t   

UNPIVOT   

(   

Sale FOR Product IN (Pen, Pencil)   

) AS UnpivotTable;