SQL Pivot

SQL PIVOT is an operator which can be used to rotate rows into separate columns. It is a T-SQL operator introduced in SQL Server 2008. Traditionally we create queries using the CASE statement and aggregate function in order to produce cross-tab reports. The SQL PIVOT operator takes a normalized table and transforms it into a new table where the columns of the new table are derived from the values in the original table.

Syntax of SQL Pivot Operator

[Source]

Example 1 of SQL PIVOT:

CREATE TABLE PIVOT_ONE(Order_Id INT, Cust_ID INT, Year INT)

Insert into PIVOT_ONE values(100, 5, 2005)
Insert into PIVOT_ONE values(101, 5, 2005)
Insert into PIVOT_ONE values(102, 5, 2007)
Insert into PIVOT_ONE values(103, 5, 2008)
Insert into PIVOT_ONE values(104, 5, 2009)
Insert into PIVOT_ONE values(105, 5, 2009)
Insert into PIVOT_ONE values(106, 5, 2009)
Insert into PIVOT_ONE values(107, 5, 2010)

Select * from PIVOT_ONE

Order_ID	Cust_ID	Year
100	5	2005
101	5	2005
102	5	2007
103	5	2008
104	5	2009
105	5	2009
106	5	2009
107	5	2010

Now, to get the no. of orders, for customer 5, for each year:

Select * from PIVOT_ONE
Pivot (count(Order_Id)
for Year in ([2005], [2006], [2007], [2008], [2009], [2010])) as Y
Where Cust_ID = 5

Cust_ID	2005	2006	2007	2008	2009	2010
5	2	0	1	1	3	1

In case of a PIVOT query:

In SELECT statement you specify the values you want to pivot on.
The From clause looks normal except for the PIVOT statement, this statement creates the value you want to show in the rows of the newly created columns.
Then we use FOR operator to list the values we want to PIVOT on.

Example 2 of SQL PIVOT:

We have a table "DayWiseIncome". And it will have 3 columns with Vendor’s Name, Week Day Name and Amount of Income. To demonstrate PIVOT, we will cover 2 queries i.e.
Query (A): Find the average for each vendor viz. Amit, Pankaj & Vinay, on day basis.
Query (B): Find the maximum income for each day for any of the 3 vendors on day basis.

--Creation of Table DialyIncome
CREATE TABLE DayWiseIncome
(VendorName NVARCHAR(50), DayName NVARCHAR(4), Amount INT)

--Filling up the data
INSERT INTO DayWiseIncome VALUES ('AMIT', 'MON', 50)
INSERT INTO DayWiseIncome VALUES ('AMIT', 'WED', 450)
INSERT INTO DayWiseIncome VALUES ('AMIT', 'TUE', 200)
INSERT INTO DayWiseIncome VALUES ('PANKAJ', 'WED', 920)
INSERT INTO DayWiseIncome VALUES ('AMIT', 'FRI', 100)
INSERT INTO DayWiseIncome VALUES ('AMIT', 'FRI', 100)
INSERT INTO DayWiseIncome VALUES ('PANKAJ', 'MON', 360)
INSERT INTO DayWiseIncome VALUES ('AMIT', 'SUN', 400)
INSERT INTO DayWiseIncome VALUES ('PANKAJ', 'FRI', 300)
INSERT INTO DayWiseIncome VALUES ('VINAY', 'TUE', 500)
INSERT INTO DayWiseIncome VALUES ('VINAY', 'TUE', 200)
INSERT INTO DayWiseIncome VALUES ('VINAY', 'SUN', 400)
INSERT INTO DayWiseIncome VALUES ('AMIT', 'MON', 900)
INSERT INTO DayWiseIncome VALUES ('VINAY', 'FRI', 900)
INSERT INTO DayWiseIncome VALUES ('VINAY', 'MON', 500)
INSERT INTO DayWiseIncome VALUES ('PANKAJ', 'SUN', 600)
INSERT INTO DayWiseIncome VALUES ('AMIT', 'FRI', 300)
INSERT INTO DayWiseIncome VALUES ('AMIT', 'WED', 500)
INSERT INTO DayWiseIncome VALUES ('AMIT', 'FRI', 300)
INSERT INTO DayWiseIncome VALUES ('PANKAJ', 'THU', 800)
INSERT INTO DayWiseIncome VALUES ('PANKAJ', 'SAT', 800)
INSERT INTO DayWiseIncome VALUES ('AMIT', 'TUE', 100)
INSERT INTO DayWiseIncome VALUES ('AMIT', 'THU', 300)
INSERT INTO DayWiseIncome VALUES ('VINAY', 'WED', 500)
INSERT INTO DayWiseIncome VALUES ('AMIT', 'SAT', 100)
INSERT INTO DayWiseIncome VALUES ('VINAY', 'SAT', 500)
INSERT INTO DayWiseIncome VALUES ('VINAY', 'THU', 800)
INSERT INTO DayWiseIncome VALUES ('PANKAJ', 'TUE', 600) 

--Select the data
SELECT * FROM DayWiseIncome

Query (A) & (B) Resolution

Query (A)

SELECT * FROM DayWiseIncome
PIVOT (AVG (Amount) FOR Dayname IN
-- FOR operator to list the values that we want to pivot in the "AvgIncome" Column.
([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) AS AvgIncome

Query (B)

SELECT * FROM DayWiseIncome
PIVOT (MAX (Amount) FOR DayName IN
-- FOR operator to list the values that we want to pivot in the “Maximum” Column.
([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) AS Maximum
WHERE VendorName IN ('AMIT')