I'm brand new to pivoting data and I could really use some help. I'm trying to create a query where the base data returns two columns: CategoryName and ProductID. The pivot table should display one row with as many columns as there are product categories with the total of the products in that category. The code I have so far is below. I think I'm missing a parentheses or something is in the wrong place. This is the error I'm receiving:
Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ')'.
I would really appreciate your help. Thank you in advance, RM.
SELECT CategoryName, ProductID
FROM (SELECT CategoryName, COUNT(ProductID) AS TotalProducts
FROM Production.Categories AS Categories
INNER JOIN Production.Products AS Products
ON Categories.categoryid = Products.categoryid) ProductTable
PIVOT(COUNT(TotalProducts)) FOR CategoryName IN (ProductID) AS C;