|
Execute the following
Microsoft SQL Server T-SQL script in Query Editor to create a cross tabulation of average list price for subcategories by colors.
The AVG, sql group by aggreage function, is applied for aggregation.
The CASE function is used to achieve of moving data from rows into columns (pivoting).
The COALESCE function replaces NULL with empty string ('').
USE AdventureWorks
GO
-- SQL crosstab case - SQL case crosstab - SQL group by aggregate
SELECT sc.Name AS Subcategory,
COALESCE(CAST(AVG(CASE
WHEN Color = 'Multi' THEN ListPrice
END) AS varchar), '') AS Multi,
COALESCE(CAST(AVG(CASE
WHEN Color = 'Silver/Black' THEN ListPrice
END) AS varchar), '') AS Silver_Black,
COALESCE(CAST(AVG(CASE
WHEN Color = 'Silver' THEN ListPrice
END) AS varchar), '') AS Silver,
COALESCE(CAST(AVG(CASE
WHEN Color = 'Grey' THEN ListPrice
END) AS varchar), '') AS Grey,
COALESCE(CAST(AVG(CASE
WHEN Color = 'White' THEN ListPrice
END) AS varchar), '') AS White,
COALESCE(CAST(AVG(CASE
WHEN Color = 'Red' THEN ListPrice
END) AS varchar), '') AS Red,
COALESCE(CAST(AVG(CASE
WHEN Color = 'Blue' THEN ListPrice
END) AS varchar), '') AS Blue,
COALESCE(CAST(AVG(CASE
WHEN Color = 'Yellow' THEN ListPrice
END) AS varchar), '') AS Yellow,
COALESCE(CAST(AVG(CASE
WHEN Color = 'Black' THEN ListPrice
END) AS varchar), '') AS Black
FROM Production.Product p
JOIN Production.ProductSubcategory sc
ON p.ProductSubcategoryID = sc.ProductSubcategoryID
GROUP BY sc.Name
ORDER BY Subcategory
GO
/* Partial results
Subcategory Multi Silver_Black Silver Grey
Mountain Bikes 1653.93
Mountain Frames 685.65
Panniers 125.00
Pedals 64.02
*/
|
