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

Answer 2

I'd written this and was testing when BICube posted his comment - and yes, this is another dynamic Pivot. You had the basic code - all you need to do is to

• Build a variable with the column name list e.g., ColList = '[21],[22],[23]'
• Use this variable in the PIVOT to provide the column list - but note you then need to make the whole statement into Dynamic SQL.

Here is the answer I wrote (Note I just made up order data rather than transcribing from your image).

CREATE TABLE #MyTable (Order_ID int, Prod_ID int, Product_Count int);
INSERT INTO #MyTable (Order_ID, Prod_ID, Product_Count)
VALUES
(100, 1, 15),
(100, 2, 12),
(100, 5, 17),
(101, 3, 10),
(101, 4, 11),
(102, 6, 12),
(102, 1, 16);

SELECT * FROM #MyTable;

DECLARE @ColList nvarchar(max) = N''
SELECT @ColList += N',' + QUOTENAME(LTRIM(STR(Prod_ID)))
    FROM (SELECT DISTINCT Prod_ID FROM #MyTable) A;

SET @ColList = STUFF(@ColList,1,1,''); -- Remove leading comma

DECLARE @PivotSQL nvarchar(max);
SET @PivotSQL = 
N'SELECT * FROM (
  SELECT
    [Order_ID],
    [prod_id],
    [product_count]
  FROM #MyTable
) QueryResults
PIVOT (
  SUM([product_count])
  FOR [prod_id]
  IN (' + @ColList + N')
) AS PivotTable;'

EXEC (@PivotSQL);

And here are the results

Order_ID    1       2       3       4       5       6
100         15      12      NULL    NULL    17      NULL
101         NULL    NULL    10      11      NULL    NULL
102         16      NULL    NULL    NULL    NULL    12