-- SQL pivot query - sql pivot crosstab - rows to columns sql - sql crosstab pivot
SELECT 'Average Cost' AS CostByDaysToManufacture,
convert(varchar,[0],1) [0 day], convert(varchar,[1],1) [1 day],
convert(varchar,[2],1) [2 days], convert(varchar,[3],1) [3 days],
convert(varchar,[4],1) [4 days], convert(varchar,[5],1) [5 days]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS VerticalData
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4], [5])
) AS pvt;
/*
CostByDaysToManufacture 0 day 1 day 2 days 3 days 4 days 5 days
Average Cost 5.09 223.88 359.11 NULL 949.41 NULL
*/
------------
The first
crosstab (cross tabulation report) set of queries is on purchase orders
issued by purchasing staff to various vendors.The T-SQL PIVOT operator
sets data in rows into columns. The crosstab cell contains a measure
such as the COUNT aggregate of items puchased by purchasing staff from a
vendor or the SUM of purchase order totals by staff by vendor.
Notice the application of the new SQL Server 2005 PIVOT operator in the second query. The first query is used to determine the staff list for the PIVOT.
Notice the application of the new SQL Server 2005 PIVOT operator in the second query. The first query is used to determine the staff list for the PIVOT.
-- SQL Server pivot - SQL Server crosstab query
USE AdventureWorks
-- Use the following query to create the IN list for the PIVOT
-- The list will become the column headers in the results
-- The entire list can be used for the pivot or a subset selected
-- SQL cte - common table expression - SQL inner join
-- SQL create comma-delimited list - SQL for xml path
DECLARE @PivotList varchar(1024)
SET @PivotList='';
WITH cteEmployeePO(Staff)
AS (SELECT left(rtrim(FirstName) + ' ' + rtrim(LastName), 30)
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN Purchasing.Vendor v
ON poh.VendorID = v.VendorID
INNER JOIN HumanResources.Employee e
ON poh.EmployeeID = e.EmployeeID
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID)
SELECT @PivotList = STUFF((SELECT ', '+QUOTENAME(Staff) AS [text()]
FROM (SELECT DISTINCT Staff from cteEmployeePO ) cte
FOR XML PATH ('')),1,1,'');
SELECT @PivotList
GO
/* [Annette Hill], [Arvind Rao], [Ben Miller], [Eric Kurjan], [Erin Hagens],
[Frank Pellow], [Fukiko Ogisu], [Gordon Hee], [Linda Meisner],
[Mikael Sandberg], [Reinout Hillmann], [Sheela Word]
*/
-- SQL pivot query
;WITH cteEmployeePO(Staff,Dealer,PurchaseOrderID)
AS (SELECT left(rtrim(FirstName) + ' ' + rtrim(LastName), 30),
left(v.Name,40),
PurchaseOrderID
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN Purchasing.Vendor v
ON poh.VendorID = v.VendorID
INNER JOIN HumanResources.Employee e
ON poh.EmployeeID = e.EmployeeID
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID)
SELECT pvt.*
FROM cteEmployeePO cte
PIVOT
(COUNT(PurchaseOrderID)
FOR Staff IN ( [Annette Hill],[Arvind Rao],[Ben Miller],
[Eric Kurjan],[Erin Hagens],[Frank Pellow],[Fukiko Ogisu],
[Gordon Hee],[Linda Meisner],[Mikael Sandberg],
[Reinout Hillmann],[Sheela Word] ) ) AS pvt
ORDER BY pvt.Dealer
/* Partial results
SQL crosstab report POs placed by purchasing staff to vendors
Dealer Annette Hill Arvind Rao Ben Miller
Advanced Bicycles 5 3 4
Allenson Cycles 4 3 5
American Bicycles and Wheels 4 1 5
American Bikes 5 2 4
Anderson's Custom Bikes 5 2 4
*/
-- ALTERNATE query to get the IN list for the PIVOT
-- SQL cte
-- SQL create comma-limited list
-- SQL inner join query
DECLARE @PivotList varchar(1024)
SET @PivotList='';
WITH cteEmployeePO(Staff,Dealer,PurchaseOrderID)
AS (SELECT left(rtrim(FirstName) + ' ' + rtrim(LastName), 30),
left(v.Name,40),
PurchaseOrderID
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN Purchasing.Vendor v
ON poh.VendorID = v.VendorID
INNER JOIN HumanResources.Employee e
ON poh.EmployeeID = e.EmployeeID
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID)
SELECT @PivotList=@PivotList+'['+Staff+'], '
FROM (SELECT DISTINCT Staff from cteEmployeePO ) cte
SET @PivotList = LEFT(@PivotList, len(@PivotList)-1)
SELECT @PivotList
GO
/* Result
[Gordon Hee], [Arvind Rao], [Fukiko Ogisu], [Ben Miller], [Eric Kurjan],
[Sheela Word], [Erin Hagens], [Frank Pellow], [Reinout Hillmann],
[Linda Meisner], [Annette Hill], [Mikael Sandberg]
*/
T-SQL Crosstab queries on list prices (ListPrice) by Color - over rows - and product Category - across columns.
Notice the variations for the list price and the application of PIVOT or CASE.
Notice the variations for the list price and the application of PIVOT or CASE.
-- SQL pivoting rows into columns
-- SQL pivot example
USE tempdb;
-- Select into create table for test dataset
SELECT Color,
Category = c.Name,
ListPrice
INTO ProdCat
FROM AdventureWorks.Production.Product p
INNER JOIN AdventureWorks.Production.ProductSubCategory sc
ON p.ProductSubCategoryID = sc.ProductSubCategoryID
INNER JOIN AdventureWorks.Production.ProductCategory c
ON sc.ProductCategoryID = c.ProductCategoryID
WHERE ListPrice > 0
AND Color IS NOT NULL
AND p.ProductSubCategoryID IS NOT NULL
-- SELECT * FROM ProdCat
-- Get column list for pivoting
SELECT DISTINCT Category
FROM ProdCat
/*
Category
Accessories
Bikes
Clothing
Components
*/
-- SQL pivot rows to columns
-- MIN aggregate function is used since there can be more than one row
-- with same color and category
-- SQL pivot crosstab query
SELECT pvt.*
FROM ProdCat pc
PIVOT
(Min(ListPrice)
FOR Category IN
( [Accessories],[Bikes],[Clothing],[Components] ) ) AS pvt
ORDER BY pvt.Color
-- SQL case function pivot
-- SQL case function crosstab
SELECT Color,
Min(CASE
WHEN Category = 'Accessories' THEN ListPrice
ELSE NULL
END) AS Accessories,
Min(CASE
WHEN Category = 'Bikes' THEN ListPrice
ELSE NULL
END) AS Bikes,
Min(CASE
WHEN Category = 'Clothing' THEN ListPrice
ELSE NULL
END) AS Clothing,
Min(CASE
WHEN Category = 'Components' THEN ListPrice
ELSE NULL
END) AS Components
FROM ProdCat
GROUP BY Color
ORDER BY Color
GO
/* Results
Color Accessories Bikes Clothing Components
Black 34.99 539.99 24.49 60.745
Blue 34.99 742.35 63.50 333.42
Grey 125.00 NULL NULL NULL
Multi NULL NULL 8.99 NULL
Red 34.99 782.99 NULL 337.22
Silver 54.99 564.99 NULL 20.24
Silver/Black NULL NULL NULL 40.49
White NULL NULL 8.99 NULL
Yellow NULL 742.35 53.99 333.42
*/
-- MAX ListPrice version
-- SQL pivot query
SELECT pvt.*
FROM ProdCat pc
PIVOT
(Max(ListPrice)
FOR Category IN
( [Accessories],[Bikes],[Clothing],[Components] ) ) AS pvt
ORDER BY pvt.Color
-- SQL case
SELECT Color,
Max(CASE
WHEN Category = 'Accessories' THEN ListPrice
ELSE NULL
END) AS Accessories,
Max(CASE
WHEN Category = 'Bikes' THEN ListPrice
ELSE NULL
END) AS Bikes,
Max(CASE
WHEN Category = 'Clothing' THEN ListPrice
ELSE NULL
END) AS Clothing,
Max(CASE
WHEN Category = 'Components' THEN ListPrice
ELSE NULL
END) AS Components
FROM ProdCat
GROUP BY Color
ORDER BY Color
GO
/* Results
Color Accessories Bikes Clothing Components
Black 34.99 3374.99 74.99 1431.50
Blue 34.99 2384.07 63.50 1003.91
Grey 125.00 NULL NULL NULL
Multi NULL NULL 89.99 NULL
Red 34.99 3578.27 NULL 1431.50
Silver 54.99 3399.99 NULL 1364.50
Silver/Black NULL NULL NULL 80.99
White NULL NULL 9.50 NULL
Yellow NULL 2384.07 53.99 1003.91
*/
-- ALL ListPrice-s comma-limited list version
-- SQL Sever pivot rows to columns
-- SQL for xml path list
SELECT Color,
Stuff((SELECT DISTINCT ', ' + Convert(VARCHAR,ListPrice) AS [text()]
FROM ProdCat pc1
WHERE pc1.Color = pc.Color
AND Category = 'Accessories'
FOR XML PATH ('')),1,1,'') AS Accessories,
Stuff((SELECT DISTINCT ', ' + Convert(VARCHAR,ListPrice) AS [text()]
FROM ProdCat pc1
WHERE pc1.Color = pc.Color
AND Category = 'Bikes'
FOR XML PATH ('')),1,1,'') AS Bikes,
Stuff((SELECT DISTINCT ', ' + Convert(VARCHAR,ListPrice) AS [text()]
FROM ProdCat pc1
WHERE pc1.Color = pc.Color
AND Category = 'Clothing'
FOR XML PATH ('')),1,1,'') AS Clothing,
Stuff((SELECT DISTINCT ', ' + Convert(VARCHAR,ListPrice) AS [text()]
FROM ProdCat pc1
WHERE pc1.Color = pc.Color
AND Category = 'Components'
FOR XML PATH ('')),1,1,'') AS Components
FROM ProdCat pc
GROUP BY Color
ORDER BY Color
GO
/* Partial results
Color Accessories Bikes
Black 34.99 1079.99, 2294.99, 2443.35, 3374.99, 539.99, 782.99
Blue 34.99 1214.85, 2384.07, 742.35
Grey 125.00 NULL
Multi NULL NULL
Red 34.99 1457.99, 2443.35, 3578.27, 782.99
Silver 54.99 2319.99, 3399.99, 564.99, 769.49
Silver/Black NULL NULL
White NULL NULL
Yellow NULL 1120.49, 1700.99, 2384.07, 742.35
*/
-- Cleanup
DROP TABLE ProdCat
GO
-- MSSQL PIVOT with two measures - crosstab query
USE AdventureWorks;
GO
WITH cteVendorPO
AS (SELECT PurchaseOrderID,
Employee = FirstName + ' ' + LastName,
Vendor = v.Name
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN HumanResources.Employee e
ON poh.EmployeeID = e.EmployeeID
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
INNER JOIN Purchasing.Vendor v
ON v.VendorID = poh.VendorID),
cteVendorTotalDue
AS (SELECT Employee = FirstName + ' ' + LastName,
Vendor = v.Name,
TotalDue
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN HumanResources.Employee e
ON poh.EmployeeID = e.EmployeeID
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
INNER JOIN Purchasing.Vendor v
ON v.VendorID = poh.VendorID)
SELECT pvt1.Vendor,
pvt1.[Erin Hagens],
[''] = '$' + convert(VARCHAR,pvt2.[Erin Hagens],1),
pvt1.[Linda Meisner],
[''] = '$' + convert(VARCHAR,pvt2.[Linda Meisner],1),
pvt1.[Sheela Word],
[''] = '$' + convert(VARCHAR,pvt2.[Sheela Word],1),
pvt1.[Frank Pellow],
[''] = '$' + convert(VARCHAR,pvt2.[Frank Pellow],1),
pvt1.[Reinout Hillmann],
[''] = '$' + convert(VARCHAR,pvt2.[Reinout Hillmann],1)
FROM cteVendorPO
PIVOT
(COUNT(PurchaseOrderID)
FOR Employee IN ( [Erin Hagens],[Linda Meisner],[Sheela Word],[Frank Pellow],[Reinout Hillmann] ) ) AS pvt1
INNER JOIN cteVendorTotalDue
PIVOT
(sum(totaldue)
FOR Employee IN ( [Erin Hagens],[Linda Meisner],[Sheela Word],[Frank Pellow],[Reinout Hillmann] ) ) AS pvt2
ON pvt1.Vendor = pvt2.Vendor
ORDER BY pvt1.Vendor
GO
Source : sqlusa.cusa
No comments:
Post a Comment