Automatically expand number of columns in SQL pivot table

Question

I need help being able to transpose a column in SQL into rows (using SQL Server 2016).

Currently I have:

SELECT Customer
    , ItemName
FROM dbSales

Gives:

Customer | ItemName
-------------------
Person1  | Product1
Person1  | Product2
...      | ...
Person1  | ProductN
Person2  | Product5
Person2  | ProductX
...      | ...
PersonN  | ProductN

I would like to produce:

Customer | Item1    | Item2    | ... | ItemN
-----------------------------------------------
Person1  | Product1 | Product2 | ... | ProductN
Person2  | Product5 | ProductX | ... | NULL
...      | ...      | ...      | ... | ...
PersonN  | ProductN | NULL     | ... | NULL

After some searching and reading I found https://community.spiceworks.com/topic/973727-sql-how-to-convert-multiple-rows-into-single-rows-with-more-columns and adapted the accepted solution to:

WITH groupitems
AS (SELECT ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Customer) AS RowID
        , *
    FROM dbSales
    )
SELECT Customer AS [CustomerName],
    MIN(Item1) AS Item1
    MIN(Item2) AS Item2
    MIN(Item3) AS Item3
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Customer) AS RowID
    , Customer
    , 'Item' + CAST(RowID AS VARCHAR) AS ItemNum
    , Item
    FROM groupitems) AS pvt
PIVOT (MIN(Item)
    FOR ItemNum in ([Item1], [Item2], [Item3])) AS PVT
GROUP BY Customer;

This will produce:

Customer | Item1    | Item2    | Item3
-----------------------------------------
Person1  | Product1 | Product2 | Product3
Person2  | ProductX | NULL     | NULL
...      | ...      | ...      | ...
PersonN  | ProductY | ProductZ | NULL

Being new to SQL, how can I adapt this to automatically expand the number of columns in the table so I don't need to hard code the number of Item columns?

Automatically expand number of columns in SQL pivot table

0 Answers0