Struggling with pivot table

Transact-SQL
1

Hello Forum,
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;

2

I am on a computer that does not have SQL server, so if the following is untested. If it does not fix the problem, please reply, someone will post the correct syntax.

Change the last line to

PIVOT(COUNT(TotalProducts) FOR CategoryName IN ([books],[magazines],[etc])) AS C;

If you don't know the categories in advance, you will have to use dynamic SQL. Example here

3

Something like this you need to select what you want to pivot for:

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 ([tea], [coffee], [sugar], [milk], [cream])
) pt

4

sz1,
I don't know the name of the categories in advance so it sounds like it will require dynamic SQL. Can you please point me toward a simple example of dynamic pivoting in SQL Server 2012? Thanks so much for your help!

RM

5

Please refer to JamesK earlier link:
Example

You can then try after you have run the SP:

EXEC dynamic_pivot
'SELECT c.categoryname, p.productid from Production..Categories as categories
INNER JOIN Production..Products as p ON (c.EmployeeID=p.EmployeeID) ',
'categoryname',
'Count(productid)'