Tuesday, January 12, 2016

PIVOT and UNPIVOT - SQL Server

Pivoting is a grouping and aggregating of data. Unpivoting is, in a sense, the inverse of pivoting.PIVOT rotates data from a state of rows to a state of columns; UNPIVOT rotates the data from columns to rows.
WITH PivotData AS
(
SELECT
< grouping column >,
< spreading column >,
< aggregation column >
FROM < source table >
)
SELECT < select list >
FROM PivotData
PIVOT( < aggregate function >(< aggregation column >)
FOR < spreading column > IN (< distinct spreading values >) ) AS P;
To understand PIVOT, let us create a Temporary Table #Books with sample records in below script:
if object_id('tempdb..#Books') is not null
drop table #Books
GO
CREATE TABLE #Books
(Book VARCHAR(150),Year INT,Price MONEY)
GO
INSERT INTO #Books VALUES('SQL 2012',2012,1300)
INSERT INTO #Books VALUES('Oracle',2012,1200)
INSERT INTO #Books VALUES('Azure',2012,1500)
INSERT INTO #Books VALUES('Azure',2014,1800)
INSERT INTO #Books VALUES('Oracle',2014,1300)
all
SELECT * FROM #Books PIVOT(SUM(Price ) FOR Book IN ([Azure], Oracle)) AS PVTTable
pivot
SELECT * INTO #BookPriceinResult FROM #Books PIVOT(SUM(price)
FOR book IN ([Azure], [Oracle])) AS PVTTable;
select * from #BookPriceinResult;
unpivotalldata
SELECT Book, Year, Price FROM #BookPriceinResult
UNPIVOT(Price FOR Book IN ([Azure], Oracle)) AS UNPVTTable;
unpivot

No comments:

Post a Comment