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.

2 Comments

Join the conversation! Your thoughts help the community grow.

rodrigo cabrera6y ago
Hi Satyaprakash, I did it in sql management studio and works very fine, but when I trayed to use the store from C# (winforms) id did'nt return the dataset where the table, so could you helpo please telling me how should I execute the store procedure from c# , thanks
- Satyaprakash Samantaray6y ago
  You should use ado.net to fetch records. Refer my other articles about ado.net