TexCopy Sql Server 2005

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