Home sql-server T-SQL Pivot and Unpivot - Principles of Work

T-SQL Pivot and Unpivot – Principles of Work

Author

Date

Category

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.

Programmers, Start Your Engines!

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.

Recent questions