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)

SELECT * FROM #Books PIVOT(SUM(Price ) FOR Book IN ([Azure], Oracle)) AS PVTTable

SELECT * INTO #BookPriceinResult FROM #Books PIVOT(SUM(price)
FOR book IN ([Azure], [Oracle])) AS PVTTable;

select * from #BookPriceinResult;

SELECT Book, Year, Price FROM #BookPriceinResult
UNPIVOT(Price FOR Book IN ([Azure], Oracle)) AS UNPVTTable;