|
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/
|
