Pivot Table Data

Question

Hello all,

I'm working on SQL Server 2012. Trying to Pivot table data, shown below

 

CategoryName	Sales	ShippingYear
Confections	27257.51	1996
Meat/Poultry	81338.06	1997
Beverages	102074.31	1997
Grains/Cereals	9219.92	1996
Seafood	65544.18	1997
Confections	80894.14	1997
Produce	12651.16	1996
Condiments	17754.78	1996
Produce	53019.98	1997
Grains/Cereals	55948.82	1997
Dairy Products	36711.37	1996
Meat/Poultry	24617.86	1996
Dairy Products	114749.78	1997
Beverages	46338	1996
Condiments	55277.6	1997
Seafood	18765.97	1996

 

to

Years	Beverages	Condiments	Confections	Dairy Products	Grains/Cereals	Meat/Poultry	Produce	Seafood
1996	46338	17754.78	27257.51	36711.37	9219.92	24617.86	12651.16	18765.97
1997	102074.31	55277.6	80894.14	114749.78	55948.82	81338.06	53019.98	65544.18

 

I tried writing following SQL Query

SELECT CategoryName, 1996_Val, 1997_Val
FROM(
SELECT
Categories.CategoryName,
Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS Sales,
YEAR(Orders.ShippedDate) AS ShippingYear
FROM Orders
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
WHERE (((Orders.ShippedDate) Between '19960101' And '19971231'))
GROUP BY Categories.CategoryID, Categories.CategoryName,YEAR(Orders.ShippedDate)
)p
PIVOT
(MAX(Sales) For ShippingYear IN(1996,1997)) AS pvt
ORDER BY Categories.CategoryID

which didn't work. Please help me to find out my mistake & achieve this.

Thnaks in advance.

Answer 1

I think you got the pivot a bit wrong. I think it should look something like this:

 

SELECT
    *
FROM
(
SELECT
    Categories.CategoryName,
    YEAR(Orders.ShippedDate) AS ShippingYear,
    CONVERT(money,([Order Details].UnitPrice*Quantity*(1-Discount)/100))*100 as Sales
FROM
    Orders
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
WHERE (((Orders.ShippedDate) Between '19960101' And '19971231'))
) AS SourceTable
PIVOT
(
    SUM(Sales)
    FOR CategoryName IN ([Confections],[Meat/Poultry],
                         [Beverages],[Grains/Cereals],[Seafood])
) AS pvt