RSS Feed

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_IDCust_IDYear
10052005
10152005
10252007
10352008
10452009
10552009
10652009
10752010

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_ID20052006200720082009 2010
5201131


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')

No comments:

Post a Comment