SQL PIVOT table

Overview:

                It gives you the transition from rows to columns. Below example explains how we can use PIVOT on table data.

Code:

Prerequisites

/*Create a tempeorary table*/

CREATE TABLE #PivotExample(Col1 VARCHAR(20), Col2 VARCHAR(20), Col3 FLOAT)

GO

/*Inserting data into the tempeorary table*/

INSERT INTO #PivotExample(Col1, Col2, Col3) VALUES('A','x',5.4)

,('A','y',3.4)

,('A','z',5.2)

,('A','x',4.4)

,('B','y',3.4)

,('B','z',1.4)

,('B','x',3.2)

,('B','z',7.1)

GO

/*Displays the data from the table*/

SELECT * FROM #PivotExample

GO

PIVOT usage

/*Pivot table logic*/

SELECT Col2, A, B

FROM (

SELECT Col1, Col2, Col3

FROM #PivotExample) actualTable

PIVOT (SUM(Col3) FOR Col1 IN (A, B)) AS pivotTable

ORDER BY Col2

GO

/*Removing tempeorary table*/

DROP TABLE #PivotExample

GO

Result:

Data in #PivotExample table

PIVOT Result (Applied on “Col1”)