select only the three fields NOTE: It’s best to that you will use when you will use the PIVOT() command. USE AdventureWorks_2012DW SELECT * FROM ( SELECT Distinct [StateProvinceName] as State, pc.[EnglishProductCategoryName], [OrderQuantity] Next we can turn it into a pivot table using the following FROM [dbo].[DimGeography] as g guideline: INNER JOIN [dbo].[FactResellerSales] as rs ON rs.SalesTerritoryKey = g.SalesTerritoryKey SELECT * FROM INNER JOIN [dbo].[DimProduct] as p (SELECT Field For Row Headings, Field for Column Headings, ON p.ProductKey = rs.ProductKey Field to be aggregated) INNER JOIN [dbo].[DimProductSubcategory]as sc FROM table ON sc.ProductSubcategoryKey = p.ProductSubcategoryKey WHERE condition) AS Basetable INNER JOIN [dbo].[DimProductCategory] as pc PIVOT ON pc.ProductCategoryKey = sc.ProductCategoryKey (SUM(field to be aggregated ) FOR field for column headings WHERE g.[EnglishCountryRegionName] = 'United States' IN (value1 from column heading to form a column, etc.) ) ) AS PivotTable Notice that AS basetable a) the GROUP BY is removed b) the aggregate fields are removed PIVOT (SUM([OrderQuantity]) c) it takes 3 joins to pull in the product category name FOR [EnglishProductCategoryName] IN (Bikes, Components, Clothing, Accessories ) ) AS Pivottable Breakdown of Pivot Query SELECT * FROM First Step: It's important that the base query include only the columns that will be needed in the final results. Any columns
NOTE: It’s best to select only the three fields that you will use when you will use the PIVOT() command. USE AdventureWorks_2012DW SELECT * FROM ( SELECT Distinct [StateProvinceName] as State, pc.[EnglishProductCategoryName], [OrderQuantity] FROM [dbo].[DimGeography] as g INNER JOIN [dbo].[FactResellerSales] as rs ON rs.SalesTerritoryKey = g.SalesTerritoryKey INNER JOIN [dbo].[DimProduct] as p ON p.ProductKey = rs.ProductKey INNER JOIN [dbo].[DimProductSubcategory]as sc ON sc.ProductSubcategoryKey = p.ProductSubcategoryKey INNER JOIN [dbo].[DimProductCategory] as pc ON pc.ProductCategoryKey = sc.ProductCategoryKey WHERE g.[EnglishCountryRegionName] = 'United States' ) AS basetable PIVOT (SUM([OrderQuantity]) FOR [EnglishProductCategoryName] IN (Bikes, Components, Clothing, Accessories ) ) AS Pivottable Next we can turn it into a pivot table using the following guideline: SELECT * FROM (SELECT Field For Row Headings, Field for Column Headings, Field to be aggregated) FROM table WHERE condition) AS Basetable PIVOT (SUM(field to be aggregated ) FOR field for column headings IN (value1 from column heading to form a column, etc.) ) AS PivotTable Notice that a) the GROUP BY is removed b) the aggregate fields are removed c) it takes 3 joins to pull in the product category name Breakdown of Pivot Query SELECT * FROM First Step: It's important that the base query include only the columns that will be needed in the final results. Any columns