Pivot Operator Using Stored Procedure In SQL Server

Introduction

Pivot is an operator in SQL Server that is used for rotating a table. Pivot operator can be used to rotatae unique values from one column, into multiple columns in the output.

 

Steps to follow

Create a table.

Create Table tblContribution
(
 Article nvarchar(50),
 Author nvarchar(50),
 Counts int
)

Insert some dummy records in it.

Insert into tblContribution values('Sql Server', 'Satyaprakash Samantaray', 1)
Insert into tblContribution values('Sql Server', 'Satyaprakash Samantaray', 2)
Insert into tblContribution values('Sql Server', 'Satyaprakash Samantaray', 3)
Insert into tblContribution values('MVC', 'Satyaprakash Samantaray', 4)
Insert into tblContribution values('MVC', 'Satyaprakash Samantaray', 5)
Insert into tblContribution values('MVC', 'Satyaprakash Samantaray', 6)
Insert into tblContribution values('C#', 'Satyaprakash Samantaray', 7)
Insert into tblContribution values('C#', 'Satyaprakash Samantaray', 8)
Insert into tblContribution values('C#', 'Satyaprakash Samantaray', 9)

Insert into tblContribution values('Sql Server', 'Kulu', 4)
Insert into tblContribution values('Sql Server', 'Kulu', 5)
Insert into tblContribution values('Sql Server', 'Kulu', 6)
Insert into tblContribution values('MVC', 'Kulu', 7)
Insert into tblContribution values('MVC', 'Kulu', 8)
Insert into tblContribution values('MVC', 'Kulu', 9)
Insert into tblContribution values('C#', 'Kulu', 1)
Insert into tblContribution values('C#', 'Kulu', 2)
Insert into tblContribution values('C#', 'Kulu', 3)

Insert into tblContribution values('Sql Server', 'Prakash', 9)
Insert into tblContribution values('Sql Server', 'Prakash', 8)
Insert into tblContribution values('Sql Server', 'Prakash', 7)
Insert into tblContribution values('MVC', 'Prakash', 6)
Insert into tblContribution values('MVC', 'Prakash', 5)
Insert into tblContribution values('MVC', 'Prakash', 4)
Insert into tblContribution values('C#', 'Prakash', 3)
Insert into tblContribution values('C#', 'Prakash', 2)
Insert into tblContribution values('C#', 'Prakash', 1)

Execute the below SQL script using "group by" clause.

Select Author, Article, SUM(Counts) as Total
from tblContribution
group by Author, Article
order by Author, Article

Here, you can see all the data, and the column names act as heading in Output.

 

 

Execute the below stored procedure SQL script using Pivot Operator.

Create Procedure Sp_Pivot
as
Begin
Select Author, [Sql Server], [MVC], [C#]
from tblContribution
pivot
(
   Sum(Counts) for Article in ([Sql Server],[MVC],[C#])
) as PivotTable
end

exec Sp_Pivot

Here, you can see all the data - column names and data values like Article names [Sql Server], [MVC], [C#] act as heading in Output.

 

 

 

Summary

We learned what Pivot Operator in SQL Server is and how to implement it using stored procedure in real time scenarios.