For this activity, we will using the AdventureWorks database. (Here is the link
ID: 3916248 • Letter: F
Question
For this activity, we will using the AdventureWorks database.
(Here is the link to download the sample database if needed. https://archive.codeplex.com/?p=msftdbprodsamples
Select the oltp file)
--Query 1--Use PIVOT to complete this query.--How many units are sold each month for each category?--List each category name, year, and the number of units sold by month. The-- names of the months are to be headers across the top and category names-- and years down the rows. Order by category name first and then year.
Explanation / Answer
SELECT Name, [Year], ISNULL([1],0) as 'Jan', ISNULL([2],0) as 'Feb',
ISNULL([3],0) as 'Mar', ISNULL([4],0) as 'Apr',
ISNULL([5],0) as 'May', ISNULL([6],0) as 'Jun',
ISNULL([7],0) as 'Jul', ISNULL([8],0) as 'Aug',
ISNULL([9],0) as 'Sep', ISNULL([10],0) as 'Oct',
ISNULL([11],0) as 'Nov', ISNULL([12],0) as 'Dec'
FROM
(Select c.Name,
DATEPART(YEAR,sh.OrderDate) 'Year',
DATEPART(MONTH,sh.OrderDate) 'Month',
so.OrderQty
From Sales.SalesOrderHeader sh INNER JOIN Sales.SalesOrderDetail so
on sh.SalesOrderID = so.SalesOrderID
INNER JOIN Production.Product p
on so.ProductID = p.ProductID
INNER JOIN Production.ProductSubcategory sc
on p.ProductSubcategoryID =sc.ProductSubcategoryID
INNER JOIN Production.ProductCategory c
on sc.ProductCategoryID = c.ProductCategoryID) as tbl
PIVOT
(
SUM(OrderQty)
FOR [Month] IN ([1], [2], [3], [4], [5], [6], [7], [8],[9], [10], [11], [12])
) AS PivotTable
Order By Name, [Year];