In the translation book is not very clear write. Explain, please, on the most simple examples with explanations of the work of these operators. Thanks in advance.

1 answer 1

PIVOT is when you need to deploy vertical data from a table field into several fields. This is usually needed for reports. For example, it may be necessary to split the data by month:

 IF OBJECT_ID('tempdb..#XYZ') IS NOT NULL DROP TABLE #XYZ GO SELECT * INTO #XYZ FROM (VALUES ('2015-01-01',10),('2015-02-01',12),('2015-03-01',11),('2015-04-01',13),('2015-05-01',10) ) as x (Period,Sales) GO SELECT * FROM (SELECT DATENAME(MONTH,Period) as PeriodMonth, Sales FROM #XYZ) up PIVOT (SUM(Sales) FOR PeriodMonth IN (January, February, March, April, May)) AS pvt; GO

UNPIVOT is a reverse process where data from many columns needs to be collected in a single column. This is usually needed to normalize the data:

 IF OBJECT_ID('tempdb..#ABC') IS NOT NULL DROP TABLE #ABC GO SELECT * INTO #ABC FROM ( VALUES(1,'A','B','C','D','E','F','G','H','K','L') ) as x(id,f1,f2,f3,f4,f5,f6,f7,f8,f9,f10); GO SELECT * FROM (SELECT * FROM #ABC ) as a UNPIVOT (ColumnValues FOR ColumnNames IN (f1,f2,f3,f4,f5,f6,f7,f8,f9,f10) ) AS Unpvt GO