Change Rows to Columns - Pivot in SQL Server

PIVOT

In this article, we will discuss PIVOT keyword in Sql Server. Pivot is responsible to  convert Rows to Columns in Sql Server.

Let's take the example.

Step 1: Declare SQL table and add some dummy data

DECLARE @ScoreTable TABLE
(
MatchType VARCHAR(50),
Year INT,
Score  INT
)

INSERT INTO @ScoreTable VALUES('OneDay',2012,1000)
INSERT INTO @ScoreTable VALUES('Test',2012,2200)
INSERT INTO @ScoreTable VALUES('OneDay',2013,800)
INSERT INTO @ScoreTable VALUES('Test',2013,370)
INSERT INTO @ScoreTable VALUES('Test',2014,1100)


Step 2: Run the Select query and see the default data

This is not mandatory step. However, just check you dummy data.

SELECT * FROM @ScoreTable

In above query, you can see we have 3 columns. MatchType, Year and Score.
We will change Year Rows data in Columns.


Step 3: Write query to covert rows to columns


SELECT * FROM @ScoreTable
PIVOT(SUM(Score)
FOR Year IN ([2012], [2013],[2014])) AS PVTTable




In above snapshot, you can see query changed year to columns and score under each column.


This is all about the article. I hope you like it.