in the transfer book is not very clearly written. Please explain to the maximum simple examples with the explanations of these operators. Thanks in advance.
Answer 1, Authority 100%
Pivot This is when you need to deploy vertical data from the table field in several fields. Usually it is necessary for reports. For example, it is necessary to split the data by months:
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 when data from many columns must be collected in one column. Usually it is necessary 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 (F1, F2, F3, F4, F5, F6, F7, F4, F5, F6, F7, F8, F9, F10)) AS UNPVT Go.