Convert rows to column with one column data SQL

Transact-SQL
1

I have a table with rows like below

StoreId | ToyId

100 | Toy1
100 | Toy2
101 | Toy1
102 | Toy1
103 | Toy1
103 | Toy2
103 | Toy3
The number of ToyID per store is not limited to 3. A store can have one or many toy ids. I want to get result as:

StoreId | ToyId1 | ToyId2 | ToyId3

100 | Toy1 | Toy2 | NULL
101 | Toy1 | NULL | NULL
102 | Toy1 | NULL | NULL
103 | Toy1 | Toy2 | Toy3
How can i achieve in SQL? It is not entire row to column pivoting, The rows dynamically get added based on number of unique rows of one column only.

2

Are you just looking for a "delimited list of ToyIDs" for each StoreID, or actually to have one-column-per-ToyID?

If you just want a "list" then there is a trick with XML what will concatenate all the "child records" into a single, delimited, list.

3

I need a column for each toyId and the ToyId is not limited to 3. It is dynamic and range to any value

4

I think the only way to handle that is using PIVOT and some dynamic SQL that figures out what the required columns are (for that dataset)

5

If you'll post useable sample data -- CREATE TABLE with INSERT statements -- I'll give you code to do that.

6 
DECLARE @toystore table
        (
         store int
       , toy varchar(10)
        );
INSERT @toystore
        (store, toy)
    VALUES
        (100, 'Toy1')
,       (100, 'Toy2')
,       (101, 'Toy1')
,       (102, 'Toy1')
,       (103, 'Toy1')
,       (103, 'Toy2')
,       (103, 'Toy3');
SET QUOTED_IDENTIFIER OFF;
DECLARE @sql varchar(MAX)= "DECLARE @toystore table(store int, toy varchar(10));
INSERT @toystore
   (store, toy)
VALUES
 (100, 'Toy1')
,(100, 'Toy2')
,(101, 'Toy1')
,(102, 'Toy1')
,(103, 'Toy1')
,(103, 'Toy2')
,(103, 'Toy3');
select store";
SELECT
        @sql+= ',' + QuoteName(t.toy)
    FROM
        @toystore t
    GROUP BY
        ',' + QuoteName(t.toy);
SET @sql += Char(10) + 'from @toystore t
pivot(
   max(toy)
      for toy in (';
SELECT
        @sql+= ',' + QuoteName(t.toy)
    FROM
        @toystore t
    GROUP BY
        ',' + QuoteName(t.toy);
SET @sql += ')
) pvt';
SET @sql = Replace(@sql, '(,[', '([');
PRINT @sql;
EXEC(@sql)

7

Thanks. It works