L A

L A

  • NA
  • 170
  • 170.9k

Pivot Table Data

Jun 15 2017 7:20 PM
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
  1. SELECT CategoryName, 1996_Val, 1997_Val
  2. FROM(  
  3. SELECT  
  4. Categories.CategoryName,  
  5. Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS Sales,  
  6. YEAR(Orders.ShippedDate) AS ShippingYear  
  7. FROM Orders  
  8. INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID  
  9. INNER JOIN Products ON [Order Details].ProductID = Products.ProductID  
  10. INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID  
  11. WHERE (((Orders.ShippedDate) Between '19960101' And '19971231'))  
  12. GROUP BY Categories.CategoryID, Categories.CategoryName,YEAR(Orders.ShippedDate)  
  13. )p  
  14. PIVOT  
  15. (MAX(Sales) For ShippingYear IN(1996,1997)) AS pvt  
  16. ORDER BY Categories.CategoryID
which didn't work. Please help me to find out my mistake & achieve this.
Thnaks in advance.

Answers (1)