Sql Server Pivot

PIVOT is used to transpose a table value expression (Like transpose in multidimensional array or matrix). In simple words it is used to convert set of rows into columns one by one. Today we will discuss Pivot relational operator.

To understand PIVOT, lets create an sales table having three columns. One for sales executive another for total sales for the year and last sales for year as below.

CREATE TABLE dbo.Sales ( Executive varchar(300), TotalSales decimal(18,2), SalesForYear int ) Go

Inserting some data into Sales table.

INSERT INTO dbo.Sales Values('Jack',250000,2016),('Jack',25340,2017),('Jack',556000,2018),('Jack',770650,2019) INSERT INTO dbo.Sales Values('Wilson',450000,2016),('Wilson',35340,2017),('Wilson',556000,2018),('Wilson',564364,2019) INSERT INTO dbo.Sales Values('Andrew',334450,2016),('Andrew',567832,2018),('Andrew',45673,2019) INSERT INTO dbo.Sales Values('Samith',234354,2016),('Samith',2346876,2017),('Samith',346575,2018),('Samith',77533,2019) INSERT INTO dbo.Sales Values('Hoper',456865,2016),('Hoper',455560,2017),('Hoper',890765,2018) Go SELECT * FROM dbo.Sales Go

OUTPUT: Executive TotalSales SalesForYear --------------------------- --------------------- ------------ Jack 250000.00 2016 Jack 25340.00 2017 Jack 556000.00 2018 Jack 770650.00 2019 Wilson 450000.00 2016 Wilson 35340.00 2017 Wilson 556000.00 2018 Wilson 564364.00 2019 Andrew 334450.00 2016 Andrew 567832.00 2018 Andrew 45673.00 2019 Samith 234354.00 2016 Samith 2346876.00 2017 Samith 346575.00 2018 Samith 77533.00 2019 Hoper 456865.00 2016 Hoper 455560.00 2017 Hoper 890765.00 2018 (18 rows affected)

Lets Calculate GandSales Made by each and every sales executive.

Using Group By Clause

SELECT Executive, Sum(TotalSales) AS GrandSales FROM DBO.Sales Group By Executive Go

OUTPUT: Executive GrandSales ------------------------------- ------------ Andrew 947955.00 Hoper 1803190.00 Jack 1601990.00 Samith 3005338.00 Wilson 1605704.00 (5 rows affected)

Lets supoose we want to transpose above result. we will use pivot as follows.

SELECT [Andrew],[Hoper],[Jack],[Samith],[Wilson] FROM (SELECT Executive,TotalSales FROM dbo.Sales) AS T1 PIVOT ( SUM(TotalSales) FOR Executive In ([Andrew],[Hoper],[Jack],[Samith],[Wilson]) ) AS T2 Go

OUTPUT: Andrew Hoper Jack Samith Wilson ----------------- -------------- -------------- -------------- ----------- 947955.00 1803190.00 1601990.00 3005338.00 1605704.00 (1 row affected)

Lets Calculate GandSales Made for every year by Group By clause.

SELECT SalesForYear, Sum(TotalSales) AS GrandSales FROM DBO.Sales Group By SalesForYear Go

OUTPUT: SalesForYear GrandSales ------------ ------------- 2016 1725669.00 2017 2863116.00 2018 2917172.00 2019 1458220.00 (4 rows affected)

Lets supoose we want to transpose above result. we will apply pivot as follows.

SELECT [2016],[2017],[2018],[2019] FROM (SELECT SalesForYear,TotalSales FROM dbo.Sales) AS T1 PIVOT ( SUM(TotalSales) FOR SalesForYear In ([2016],[2017],[2018],[2019]) ) AS T2

OUTPUT: 2016 2017 2018 2019 ---------------- --------------- -------------- ------------ 1725669.00 2863116.00 2917172.00 1458220.00 (1 row affected)

Let supoose we want to get TotalSales by all executives in every years we can simple create select query.

SELECT * FROM dbo.Sales Go

OUTPUT: Executive TotalSales SalesForYear --------------------------- --------------------- ------------ Jack 250000.00 2016 Jack 25340.00 2017 Jack 556000.00 2018 Jack 770650.00 2019 Wilson 450000.00 2016 Wilson 35340.00 2017 Wilson 556000.00 2018 Wilson 564364.00 2019 Andrew 334450.00 2016 Andrew 567832.00 2018 Andrew 45673.00 2019 Samith 234354.00 2016 Samith 2346876.00 2017 Samith 346575.00 2018 Samith 77533.00 2019 Hoper 456865.00 2016 Hoper 455560.00 2017 Hoper 890765.00 2018 (18 rows affected)

But above result set is difficult to understand. We can make it more understandable by using pivot as below.

SELECT [SalesForYear],[Andrew],[Hoper],[Jack],[Samith],[Wilson] FROM (SELECT Executive,TotalSales,SalesForYear FROM dbo.Sales) AS T1 PIVOT ( SUM(TotalSales) FOR Executive In ([Andrew],[Hoper],[Jack],[Samith],[Wilson]) ) AS T2 Go

OUTPUT: SalesForYear Andrew Hoper Jack Samith Wilson ------------ -------------- -------------- -------------- -------------- ------------ 2016 334450.00 456865.00 250000.00 234354.00 450000.00 2017 NULL 455560.00 25340.00 2346876.00 35340.00 2018 567832.00 890765.00 556000.00 346575.00 556000.00 2019 45673.00 NULL 770650.00 77533.00 564364.00 (4 rows affected)

Please note Every time we should have to specify static column name, it means we should know about each and every column value. its well suitable for few value which will never changed in future.

What happens if these column values will be changed dynamically in table. above pivot queries will completely fails.

To get rid off static column values we can traverse first all column values and than apply pivot as follows.

/*Declare Variable*/ DECLARE @Pivot_Column [nvarchar](max) DECLARE @Query [nvarchar](max) /*Select Pivot Column*/ SELECT @Pivot_Column= COALESCE(@Pivot_Column+',','')+ '['+Executive+']' FROM (SELECT DISTINCT Executive FROM dbo.Sales)Tab SET @Query = 'SELECT [SalesForYear], '+@Pivot_Column+' FROM (SELECT Executive,TotalSales,SalesForYear FROM dbo.Sales) AS T1 PIVOT ( SUM(TotalSales) FOR Executive In ('+@Pivot_Column+') ) AS T2 ' EXEC sp_executesql @Query Go

OUTPUT: SalesForYear Andrew Hoper Jack Samith Wilson ------------ -------------- -------------- -------------- -------------- ------------ 2016 334450.00 456865.00 250000.00 234354.00 450000.00 2017 NULL 455560.00 25340.00 2346876.00 35340.00 2018 567832.00 890765.00 556000.00 346575.00 556000.00 2019 45673.00 NULL 770650.00 77533.00 564364.00 (4 rows affected

If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.


Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.

 
 

{{c.Content}}

Comment By: {{c.Author}}  On:   {{c.CreatedDate|date:'dd/MM/yyyy'}} / Reply


Categories