create two tables using one

Question

CREATE TABLE ItemsOfStores ( Item int, Store int );

For my example above, that data would contain:

INSERT INTO ItemsOfStores (Item,Store)
    VALUES (1,1),(1,3),(4,1),(4,3),(5,1),(5,3),(2,1),
           (2,2),(2,4),(3,1),(3,2),(3,4);

I need the SQL to insert the results into 2 tables:

CREATE TABLE StoreGroups ( GroupId int, Store int )
CREATE TABLE ItemsOfGroups ( GroupId int, Item int )

In my example above, items 1, 4 and 5 are at stores 1 and 3, then there would be a group of stores composed of stores 1 and 3, I'll call that group 1. So group 1 holds stores 1 and 3, so that would put (1,1),(1,3) into StoreGroups, and the items 1, 4 and 5 are in that group, so (1,1),(1,4),(1,5) would go into ItemsOfGroups.

Also in my example, items 2 and 3 are at stores 1, 2 and 4, then there would be a second group of stores composed of stores 1, 2 and 4, I'll call that group 2. So (2,1),(2,2),(2,4) would go into StoreGroups, and (2,2),(2,3) would go into ItemsOfGroups.

So the final results would of this small example would fill StoreGroups with (1,1),(1,3),(2,1),(2,2),(2,4), and ItemsOfGroups with (1,1),(1,4),(1,5),(2,2),(2,3)

Answer 1

I guess you want something like this.

    CREATE TABLE ItemsOfStores ( Item int, Store int );


    INSERT INTO ItemsOfStores (Item,Store)
        VALUES (1,1),(1,3),(4,1),(4,3),(5,1),(5,3),(2,1),
               (2,2),(2,4),(3,1),(3,2),(3,4);


    CREATE TABLE StoreGroups ( GroupId int, Store int );
    CREATE TABLE ItemsOfGroups ( GroupId int, Item int );

    Insert into StoreGroups

    SELECT z.GroupID,  value 
    FROM (
            Select    ROW_NUMBER() OVER(ORDER BY x.Store ASC)as GroupID, x.Store , x.Item
            from 
                ( 
                    select 

                    T3.Store, 

                    stuff((SELECT  ', ' + cast(Item as varchar(10))
                            FROM (
                                    select Item, 
                                        stuff((SELECT  ', ' + cast(Store as varchar(10))
                                                FROM ItemsOfStores t2
                                                where t2.Item = t1.Item
                                                order by Store
                                                FOR XML PATH('')),1,1,'') as Store
                                    from ItemsOfStores t1
                                    group by Item
                                )t4
                            where t4.Store = t3.Store
                            order by Item
                            FOR XML PATH('')),1,1,'') 
                    as Item

                    from (
                                            select Item, 
                                              stuff((SELECT  ', ' + cast(Store as varchar(10))
                                                       FROM ItemsOfStores t2
                                                       where t2.Item = t1.Item
                                                       order by Store
                                                       FOR XML PATH('')),1,1,'') as Store
                                            from ItemsOfStores t1
                                            group by Item


                                    ) t3
                    group by T3.Store
                )  x
        )z
    CROSS APPLY STRING_SPLIT(z.Store, ',');

    Select * from StoreGroups

Returns:

    GroupId Store
    1       1
    1       2
    1       4
    2       1
    2       3

same logic:

    Insert into ItemsOfGroups
    -- Use String Split (SQL2016+) function to split merged colums back into seperate values with their related GroupID
    SELECT z.GroupID,  value 
    FROM (-- Add GroupID which is related to both merged Item rows and merged store rows
            Select    ROW_NUMBER() OVER(ORDER BY x.Store ASC)as GroupID, x.Store , x.Item
            from 
                ( -- Merge Item values into one row (grouped by Grouped result of Store)
                    select Store, 
                      stuff((SELECT  ', ' + cast(Item as varchar(10))
                               FROM (
                                        select Item, 
                                          stuff((SELECT  ', ' + cast(Store as varchar(10))
                                                   FROM ItemsOfStores t2
                                                   where t2.Item = t1.Item
                                                   order by Store
                                                   FOR XML PATH('')),1,1,'') as Store
                                        from ItemsOfStores t1
                                        group by Item
                                    )t4
                               where t4.Store = t3.Store
                               order by Item
                               FOR XML PATH('')),1,1,'') as Item
                    from ( -- Merge Store values into one row (grouped by Item)
                                select Item, 
                                  stuff((SELECT  ', ' + cast(Store as varchar(10))
                                           FROM ItemsOfStores t2
                                           where t2.Item = t1.Item
                                           order by Store
                                           FOR XML PATH('')),1,1,'') as Store
                                from ItemsOfStores t1
                                group by Item


                        ) t3
                    group by T3.Store
                )  x
            )z
        CROSS APPLY STRING_SPLIT(z.Item, ',');

    Select * from ItemsOfGroups

Returns:

    GroupId Item
    1       2
    1       3
    2       1
    2       4
    2       5