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.