Pivot operator in sql server

What is Pivot operator in SQL Server?

Pivot is a sql server operator that can be used to turn unique values from one column, into multiple columns in the output, there by effectively rotating a table. 

We can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values. 

Syntax:- 

  

SELECT <non-pivoted column>,
    [first pivoted column] AS <column name>,
    [second pivoted column] AS <column name>,
    ...
    [last pivoted column] AS <column name>

FROM

    (<SELECT query that produces the data>)
    AS <alias for the source query>

PIVOT
(
    <aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>

Now, we are going to explation power of PIVOT with the help of example. 

Let's first create table named tblAuthor and insert some records as shown in below. 

  
Create Table tblAuthore int
)

----------------Insert records in 'tblAuthor'----------------

INSERT INTO tblAuthor VALUES('Santosh kumar singh','Bihar','CSharp Programming',250)
INSERT INTO tblAuthor VALUES('Reena kumari','Punjab','ASP.NET Programming',430)
INSERT INTO tblAuthor VALUES('ER. Gagan','Punjab','Digital Electronics',690)

INSERT INTO tblAuthor VALUES('Santosh kumar singh','Punjab','CSharp Programming',250)
INSERT INTO tblAuthor VALUES('Reena kumari','Bihar','ASP.NET Programming',410)
INSERT INTO tblAuthor VALUES('ER. Gagan','Delhi','Digital Electronics',620)

INSERT INTO tblAuthor VALUES('Santosh kumar singh','Delhi','CSharp Programming',460)
INSERT INTO tblAuthor VALUES('Reena kumari','Delhi','ASP.NET Programming',410)
INSERT INTO tblAuthor VALUES('ER. Gagan','Bihar','Digital Electronics',410)


SELECT * FROM tblAuthor

Now you can see in the result-set above that there are three distinct values of customer name (ER. Gagan, Reena kumari, Santosh kumar singh) and three countries (Bihar, Delhi, Punjab) as shown in below.

  

Select State,AuthorName, SUM(Price) as TotalPrice
from tblAuthor
GROUP BY State,AuthorName
ORDER BY State,AuthorName

We can clearly see State has three values (Bihar, Delhi and Punjab).

Now, we are using power of PIVOT operator that can actually rotate the table as shown in below.

We can clearly see State has three values (Bihar, Delhi and Punjab).

Now, we are using power of PIVOT operator that can actually rotate the table as shown in below.

  

Select * from tblAuthor
PIVOT
(
 SUM(Price) FOR State IN ([Bihar],[Punjab],[Delhi])
) AS Pivotable

Now, another example of using power of PIVOT operator as shown in below.

  

Select AuthorName, Bihar, Delhi, Punjab
from 
(
   Select AuthorName, State, Price from tblAuthor
) as SourceTable
Pivot
(
 Sum(Price) for State in (Bihar, Delhi,Punjab)
) as PivotTable