SQL Server Pivot Operator Syntax with Examples
The Pivoting is an operation that transforms the data from rows to a state of columns to pivot the data used for different reporting needs to summarize and analyze the data. The Pivot helps to you fit the certain amount of data in less space.It helps to analyze the data presented as the intersection of dimensions represented by rows and columns.
Syntax:
SELECT <col-list>
FROM <source> PIVOT (<aggre-func>(<aggre-col>)
FOR <spreading-col> IN (<spreading-col-elements>)) as PVT
Example :
USE [TAMATAM]
GO
SELECT [Sales_Region] ,[Sales_Period],[Cust_Segment],[NetSales]
FROM [dbo].[SalesTable]
GO
The Pivoting is an operation that transforms the data from rows to a state of columns to pivot the data used for different reporting needs to summarize and analyze the data. The Pivot helps to you fit the certain amount of data in less space.It helps to analyze the data presented as the intersection of dimensions represented by rows and columns.
Syntax:
SELECT <col-list>
FROM <source> PIVOT (<aggre-func>(<aggre-col>)
FOR <spreading-col> IN (<spreading-col-elements>)) as PVT
Example :
USE [TAMATAM]
GO
SELECT [Sales_Region] ,[Sales_Period],[Cust_Segment],[NetSales]
FROM [dbo].[SalesTable]
GO
Sample Source Data used for Pivot Operation :
Sales_Region | Sales_Period | Cust_Segment | NetSales |
East | 201501 | Automobile | 7276 |
East | 201502 | Electronics | 2402 |
East | 201503 | Pharma | 7097 |
East | 201504 | Construction | 6521 |
East | 201505 | Banking | 9966 |
East | 201506 | Energy | 2917 |
East | 201507 | Automobile | 5220 |
East | 201508 | Electronics | 6304 |
East | 201509 | Pharma | 4060 |
East | 201510 | Construction | 7098 |
East | 201511 | Banking | 5688 |
East | 201512 | Energy | 4433 |
West | 201501 | Automobile | 7627 |
West | 201502 | Electronics | 2340 |
West | 201503 | Pharma | 3530 |
West | 201504 | Construction | 5594 |
West | 201505 | Banking | 8356 |
West | 201506 | Energy | 7172 |
West | 201507 | Automobile | 2546 |
West | 201508 | Electronics | 5645 |
West | 201509 | Pharma | 9049 |
West | 201510 | Construction | 5443 |
West | 201511 | Banking | 7313 |
West | 201512 | Energy | 6694 |
South | 201501 | Automobile | 5573 |
South | 201502 | Electronics | 7319 |
South | 201503 | Pharma | 7628 |
South | 201504 | Construction | 5158 |
South | 201505 | Banking | 8457 |
South | 201506 | Energy | 9548 |
South | 201507 | Automobile | 7640 |
South | 201508 | Electronics | 2481 |
South | 201509 | Pharma | 2300 |
South | 201510 | Construction | 3198 |
South | 201511 | Banking | 8492 |
South | 201512 | Energy | 5331 |
North | 201501 | Automobile | 7862 |
North | 201502 | Electronics | 3915 |
North | 201503 | Pharma | 1977 |
North | 201504 | Construction | 9125 |
North | 201505 | Banking | 7499 |
North | 201506 | Energy | 6685 |
North | 201507 | Automobile | 5030 |
North | 201508 | Electronics | 2922 |
North | 201509 | Pharma | 7804 |
North | 201510 | Construction | 4147 |
North | 201511 | Banking | 5375 |
North | 201512 | Energy | 4471 |
Pivot model-1: [NetSales] summary by [Cust_Segment] for [Sales_Region]:
SELECT [Cust_Segment],[East],[West],[South],[North]
FROM
( SELECT [Sales_Region] ,[Cust_Segment],[NetSales]
FROM [dbo].[SalesTable]
) T1
PIVOT ( SUM(NetSales)
FOR [Sales_Region] IN ( "East","West", "South","North")
) as PVT
Order by [Cust_Segment]
Go
------------------------------------- OR -----------------------------
SELECT [Cust_Segment],
SUM([East]) [East], SUM([West]) [West], SUM([South]) [South],
SUM([North]) [North]
FROM (
SELECT [Sales_Region] ,[Cust_Segment],[NetSales]
FROM [dbo].[SalesTable]
) T1
PIVOT ( SUM (NetSales)
FOR [Sales_Region] IN ( "East","West", "South","North")
) as PVT
Group by [Cust_Segment]
Go
Output:
Pivot model-2: [NetSales] summary by [Cust_Segment] for [Sales_Period]:
SELECT [Cust_Segment],
SUM([201501])[201501],SUM([201502]) [201502],SUM([201503]) [201503],
SUM([201504]) [201504],
SUM([201505])[201505],SUM([201506]) [201506],SUM([201507]) [201507],
SUM([201508]) [201508],
SUM([201509])[201509],SUM([201510]) [201510],SUM([201511]) [201511],
SUM([201512]) [201512]
FROM(
SELECT [Cust_Segment],[Sales_Period],[NetSales]
FROM [dbo].[SalesTable]
) T1
PIVOT ( SUM(NetSales)
FOR [Sales_Period] IN ([201501],[201502],[201503],[201504],[201505],[201506],
[201507], [201508], [201509], [201510], [201511], [201512])
) as PVT
Group by [Cust_Segment]
Go
Output :
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------