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.
  1. Create Table tblContribution
  2. (
  3. Article nvarchar(50),
  4. Author nvarchar(50),
  5. Counts int
  6. )
Insert some dummy records in it.
  1. Insert into tblContribution values('Sql Server', 'Satyaprakash Samantaray', 1)
  2. Insert into tblContribution values('Sql Server', 'Satyaprakash Samantaray', 2)
  3. Insert into tblContribution values('Sql Server', 'Satyaprakash Samantaray', 3)
  4. Insert into tblContribution values('MVC', 'Satyaprakash Samantaray', 4)
  5. Insert into tblContribution values('MVC', 'Satyaprakash Samantaray', 5)
  6. Insert into tblContribution values('MVC', 'Satyaprakash Samantaray', 6)
  7. Insert into tblContribution values('C#', 'Satyaprakash Samantaray', 7)
  8. Insert into tblContribution values('C#', 'Satyaprakash Samantaray', 8)
  9. Insert into tblContribution values('C#', 'Satyaprakash Samantaray', 9)
  11. Insert into tblContribution values('Sql Server', 'Kulu', 4)
  12. Insert into tblContribution values('Sql Server', 'Kulu', 5)
  13. Insert into tblContribution values('Sql Server', 'Kulu', 6)
  14. Insert into tblContribution values('MVC', 'Kulu', 7)
  15. Insert into tblContribution values('MVC', 'Kulu', 8)
  16. Insert into tblContribution values('MVC', 'Kulu', 9)
  17. Insert into tblContribution values('C#', 'Kulu', 1)
  18. Insert into tblContribution values('C#', 'Kulu', 2)
  19. Insert into tblContribution values('C#', 'Kulu', 3)
  21. Insert into tblContribution values('Sql Server', 'Prakash', 9)
  22. Insert into tblContribution values('Sql Server', 'Prakash', 8)
  23. Insert into tblContribution values('Sql Server', 'Prakash', 7)
  24. Insert into tblContribution values('MVC', 'Prakash', 6)
  25. Insert into tblContribution values('MVC', 'Prakash', 5)
  26. Insert into tblContribution values('MVC', 'Prakash', 4)
  27. Insert into tblContribution values('C#', 'Prakash', 3)
  28. Insert into tblContribution values('C#', 'Prakash', 2)
  29. Insert into tblContribution values('C#', 'Prakash', 1)
Execute the below SQL script using "group by" clause.
  1. Select Author, Article, SUM(Counts) as Total
  2. from tblContribution
  3. group by Author, Article
  4. 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.
  1. Create Procedure Sp_Pivot
  2. as
  3. Begin
  4. Select Author, [Sql Server], [MVC], [C#]
  5. from tblContribution
  6. pivot
  7. (
  8. Sum(Counts) for Article in ([Sql Server],[MVC],[C#])
  9. ) as PivotTable
  10. end
  1. 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.