SQL Server pivot query - questions

Question

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?

Answer 1

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