Search This Blog

New Features - Pivot and Unpivot

PIVOT And UNPIVOT

If you have ever worked with Microsoft Access, you might have used the TRANSFORM statement to create a crosstab query. Even though the similar functionality was missed in sql server 2000, we can achieve the same in SQL Server 2005 via the PIVOT operator with the SELECT statement. The PIVOT operator can be used to transform a set of rows into columns while the UNPIVOT operator complements the PIVOT operator by allowing you to turn columns into rows.

Here is an example

 

 

DECLARE @tblStudents table (
StudentID INT IDENTITY,
StudentName VARCHAR(100),
CollegeName VARCHAR(100),
JoinedOn DATETIME
)
INSERT INTO @tblStudents
SELECT 'Zero','College_One','20010101'
UNION ALL
SELECT 'One','College_One','20010102'
UNION ALL
SELECT 'Two','College_One','20010202'
UNION ALL
SELECT 'Three','College_Two','20010202'
UNION ALL
SELECT 'Four','College_One','20020302'
UNION ALL
SELECT 'Five','College_One','20030202'
UNION ALL
SELECT 'Six','College_Two','20040202'
UNION ALL
SELECT 'Seven','College_Two','20040202'
UNION ALL
SELECT 'Eight','College_Three','20040202'

SELECT *
FROM @tblStudents

SELECT CollegeName,
[2001] AS Yr2001,
[2002] AS Yr2002,
[2003] AS Yr2003,
[2004] AS Yr2004
FROM ( SELECT StudentName,
CollegeName,
YEAR(JoinedOn) Yr
FROM @tblStudents ) ts
PIVOT ( Count(StudentName) FOR Yr IN ( [2001] , [2002] , [2003] , [2004] ) ) tp

 

Here is another example

Example2

 

USE AdventureWorks

SELECT EmployeeID,
[2002] Yr2002,
[2003] Yr2003,
[2004] Yr2004
FROM ( SELECT YEAR(OrderDate) OrderYear,
EmployeeID,
TotalDue
FROM Purchasing.PurchaseOrderHeader ) poh
PIVOT ( SUM(TotalDue) FOR OrderYear IN ( [2002] , [2003] ,[2004] ) ) pvt
ORDER BY EmployeeID

 

 

Example 3:

 

 

DECLARE @tab TABLE (id INT IDENTITY, status VARCHAR(1000))
INSERT INTO @Tab SELECT 'Completed'
INSERT INTO @Tab SELECT 'Submitted'
INSERT INTO @Tab SELECT 'InProgress'
INSERT INTO @Tab SELECT 'Submitted'
INSERT INTO @Tab SELECT 'Completed'


SELECT [Submitted],[Completed] , [InProgress]
FROM (

SELECT Status, COUNT(*) cnt
FROM @Tab
GROUP BY Status )t
PIVOT (SUM(cnt) FOR Status IN ([Submitted],[Completed] , [InProgress]) )a

1 comment:

Amit S Patriwala said...

Really Good Article

i learn so many thing

thnks