I have a table with 3 columns: order_id, product_id, product_count
The first column is an order passed by a client, the second is the product unique id and the third is the quantity of a product bought in an order.
I want to create a matrix of order_id
/ product_id
with number of items bought.
As a result I would like to have something that looks like this:
If I make this request:
SELECT * FROM (SELECT [order_id], [prod_id], [product_count] FROM mydb.dbo.mytable) QueryResults PIVOT (SUM([product_count]) FOR [prod_id] IN ([21], [22], [23]) ) AS PivotTable
My issue is that I have more than 200 different products to retrieve. Is there a way to make it without entering all values?
Advertisement
Answer
Based on @seanb answer that saved me, I tried to replace the NULL values with 0. I understood the principle (the base). Here is how I updated the SQL request to replace the NULL values.
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX), @PivotColumnNames AS NVARCHAR(MAX), @PivotSelectColumnNames AS NVARCHAR(MAX) --Get distinct values of the PIVOT Column SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','') + QUOTENAME(prod_id) FROM (SELECT DISTINCT prod_id FROM #MyTable) AS prod_id --Get distinct values of the PIVOT Column with isnull SELECT @PivotSelectColumnNames = ISNULL(@PivotSelectColumnNames + ',','') + 'ISNULL(' + QUOTENAME(prod_id) + ', 0) AS ' + QUOTENAME(prod_id) FROM (SELECT DISTINCT prod_id FROM #MyTable) AS prod_id --Prepare the PIVOT query using the dynamic SET @DynamicPivotQuery = N'SELECT order_id, ' + @PivotSelectColumnNames + ' FROM #MyTable PIVOT(SUM(product_count) FOR prod_id IN (' + @PivotColumnNames + ')) AS PivotTable' --Execute the Dynamic Pivot Query EXEC sp_executesql @DynamicPivotQuery