Wednesday, 22 March 2017

How to Pivot the Data in SQL Server

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

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 Professional
--------------------------------------------------------------------------------------------------------


No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts