datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to use the UNPIVOT operator?

Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to unpivot columns into rows using the UNPIVOT and optionally the UNION ALL operators. Column types must be same for the UNPIVOT operator. AttributeValue(aValue) must be the same data type for the UNION ALL operator

USE tempdb;

 

-- Create a table for testing the UNPIVOT operator - opposite of PIVOT

SELECT ID = 'I' + convert(VARCHAR,BusinessEntityID) + 'D' +

                  convert(VARCHAR,DepartmentID),

       StartDate,

       EndDate

INTO   EmployeeDept

FROM   AdventureWorks2008.HumanResources.EmployeeDepartmentHistory

WHERE  EndDate IS NOT NULL

GO

 

--Unpivot the table using the T-SQL UNPIVOT operator

SELECT *

FROM

   (SELECT ID, StartDate, EndDate

   FROM EmployeeDept) p

UNPIVOT

   (Date FOR DateType IN

      (StartDate, EndDate)

)AS unpvt;

GO

 

/*

ID          Date        DateType

I4D1        1998-01-05  StartDate

I4D1        2000-06-30  EndDate

I16D5       1998-01-20  StartDate

I16D5       1999-08-15  EndDate

I224D7      1999-02-08  StartDate

I224D7      2001-10-01  EndDate

.....

*/

------------

 

-- SQL UNPIVOT using the UNION ALL operator

-- SQL change columns into rows

/* INPUT DATA in CTE

SalesOrderID      OrderDate   Sales       AccountNumber

43659             2001-07-01  $23,153.23  10-4020-000676

43660             2001-07-01  $1,457.33   10-4020-000117

43661             2001-07-01  $36,865.80  10-4020-000442

43662             2001-07-01  $32,474.93  10-4020-000227

......

*/
;WITH cteSO AS

(SELECT SalesOrderID, OrderDate = convert(date,OrderDate),

        Sales='$'+convert(varchar,TotalDue,1), AccountNumber

FROM AdventureWorks.Sales.SalesOrderHeader)

SELECT SalesOrderID,Attribute='OrderDate', aValue=convert(varchar,OrderDate)

FROM cteSO

UNION ALL

SELECT SalesOrderID,Attribute='Sales', aValue=Sales FROM cteSO

UNION ALL

SELECT SalesOrderID,Attribute='AccountNumber', aValue=AccountNumber FROM cteSO

ORDER BY SalesOrderID, Attribute

/*

SalesOrderID      Attribute               aValue

43659             AccountNumber           10-4020-000676

43659             OrderDate               2001-07-01

43659             Sales                   $23,153.23

43660             AccountNumber           10-4020-000117

43660             OrderDate               2001-07-01

43660             Sales                   $1,457.33

*/

------------


/****************** PIVOT SALES DATA *******************/
;WITH CTE
AS (SELECT YEAR = YEAR(orderDate),
    QUARTER = DatePart(qq,OrderDate),
    Sales = Sum(TotalDue)
    FROM Sales.SalesOrderHeader
    GROUP BY YEAR(orderDate), DatePart(qq,OrderDate))
 
 
SELECT YEAR, Q1 = [1], Q2 = [2], Q3 = [3], Q4 = [4]
INTO #PivotTable
FROM (SELECT * FROM CTE) AS PivotInput
PIVOT
(SUM(Sales) -- Aggregate for cells
FOR QUARTER IN ( [1],[2],[3],[4] ) ) AS PivotOutput
ORDER BY YEAR;
 
SELECT * FROM #PivotTable
GO
 
 
/****************** UNPIVOT DATA JUST PIVOTED *******************/
 
SELECT      YEAR,
            Quarter,
            Sales
FROM (      SELECT *
            FROM #PivotTable) p
UNPIVOT
(Sales
 FOR Quarter IN ( [Q1],[Q2],[Q3],[Q4] ) ) AS unpvt
 ORDER BY YEAR,
          Quarter;
GO
 
 
-- Cleanup

DROP TABLE #PivotTable
-- T-SQL UNPIVOT columns with different data types
SELECT *
FROM   (SELECT ProductID,
               Color,
               ProductNumberStr = CONVERT(VARCHAR(128),ProductNumber),
               ListPriceStr         = CONVERT(VARCHAR(128),ListPrice),
               StandardCostStr      = CONVERT(VARCHAR(128),StandardCost),
               ProductNameStr = CONVERT(VARCHAR(128),Name)
        FROM   AdventureWorks2008.Production.Product
        WHERE  Color IS NOT NULL) p
       UNPIVOT
       (AttribValue
        FOR ProdAttrib IN ( [ProductNumberStr],[ListPriceStr],[StandardCostStr],[ProductNameStr] ) ) AS unpvt;
GO
/*
ProductID   Color AttribValue ProdAttrib
895   Blue  333.42      ListPriceStr
895   Blue  199.85      StandardCostStr
895   Blue  LL Touring Frame - Blue, 50   ProductNameStr
896   Blue  FR-T67U-54  ProductNumberStr
896   Blue  333.42      ListPriceStr
896   Blue  199.85      StandardCostStr
896   Blue  LL Touring Frame - Blue, 54   ProductNameStr
*/
------------


------------
-- T-SQL UNPIVOT columns with different data types
------------
SELECT *
FROM   (SELECT ProductID,
               Color,
               ProductNumberStr = CONVERT(VARCHAR(128),ProductNumber),
               ListPriceStr         = CONVERT(VARCHAR(128),ListPrice),
               StandardCostStr      = CONVERT(VARCHAR(128),StandardCost),
               ProductNameStr = CONVERT(VARCHAR(128),Name)
        FROM   AdventureWorks2008.Production.Product
        WHERE  Color IS NOT NULL) p
       UNPIVOT
       (AttribValue
        FOR ProdAttrib IN ( [ProductNumberStr],[ListPriceStr],[StandardCostStr],[ProductNameStr] ) ) AS unpvt;
GO
/*
ProductID   Color AttribValue                   ProdAttrib
895         Blue  333.42                        ListPriceStr
895         Blue  199.85                        StandardCostStr
895         Blue  LL Touring Frame - Blue, 50   ProductNameStr
896         Blue  FR-T67U-54                    ProductNumberStr
896         Blue  333.42                        ListPriceStr
896         Blue  199.85                        StandardCostStr
896         Blue  LL Touring Frame - Blue, 54   ProductNameStr
*/

------------

Related article:

http://www.sqlusa.com/bestpractices2005/dynamicpivot/

 

Exam Prep 70-461
Exam 70-461