how to query to create a grouping of sets

Question

I have a normal joining table with two columns that are keys to other tables. The records are customers mapped to a range of assets.

asset_map  
-----------

customer_id    asset_id
-----------------------
     1            1
     1            2
     2            1
     2            2
     3            1
     3            2
     3            3
     4            1
     4            2

There are around 10 assets, each customer can be mapped to any combination of those. What I would like to achieve is something like this:

grouping    customer_id    asset_id
------------------------------------
    1            1            1
    1            1            2
    1            2            1
    1            2            2  
- - - - - - - - - - - - - - - - - - 
    2            3            3
    2            3            1
    2            3            2  
- - - - - - - - - - - - - - - - - - 
    1            4            1
    1            4            2

Notice that customer 1,2 and 4 fall into the same grouping as they are mapped to assets 1 and 2. Customer 3 is not in the grouping as its in 1,2 and 3.

Answer 1

You can try something like this

DECLARE @Table TABLE(
    customer_id INT,
    asset_id INT
)

INSERT INTO @Table (customer_id,asset_id) SELECT 1,1
INSERT INTO @Table (customer_id,asset_id) SELECT 1,2
INSERT INTO @Table (customer_id,asset_id) SELECT 2,1
INSERT INTO @Table (customer_id,asset_id) SELECT 2,2
INSERT INTO @Table (customer_id,asset_id) SELECT 3,1
INSERT INTO @Table (customer_id,asset_id) SELECT 3,2
INSERT INTO @Table (customer_id,asset_id) SELECT 3,3
INSERT INTO @Table (customer_id,asset_id) SELECT 4,1
INSERT INTO @Table (customer_id,asset_id) SELECT 4,2

SELECT  * 
FROM    @Table

;WITH CTE AS (
     SELECT *,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY customer_id, asset_id) RowNumber
     FROM @Table
),
CTELinked AS (
     SELECT customer_id,asset_id, RowNumber,
       CAST(CAST(asset_id AS VARCHAR(10)) AS VARCHAR(MAX)) Grouped
     FROM CTE
     WHERE RowNumber = 1
     UNION ALL
     SELECT c.customer_id,c.asset_id, c.RowNumber,
       CAST(CTELinked.Grouped + ',' + CAST(c.asset_id AS VARCHAR(10)) AS VARCHAR(MAX))
     FROM CTE c INNER JOIN
       CTELinked ON c.customer_id = CTELinked.customer_id AND c.RowNumber  = CTELinked.RowNumber + 1
),
CTEConcat AS (
     SELECT CTELinked.*
     FROM CTELinked INNER JOIN
       (
        SELECT customer_id, MAX(RowNumber) MaxRows
        FROM CTELinked
        GROUP BY customer_id
       ) Maxes ON CTELinked.customer_id = Maxes.customer_id AND CTELinked.RowNumber = Maxes.MaxRows
)
SELECT  g.GroupingID,
     c.customer_id,
     t.asset_id
FROM    CTEConcat c INNER JOIN
     (
      SELECT Grouped,
        ROW_NUMBER() OVER(ORDER BY Grouped) GroupingID
      FROM (
         SELECT DISTINCT 
           Grouped
         FROM CTEConcat
        ) sub
     ) g ON c.Grouped = g.Grouped INNER JOIN
     @Table t ON c.customer_id = t.customer_id

EDIT using sp and CURSOR X-(

this should help a bit

DECLARE @customer_id INT
    DECLARE CUR CURSOR FOR 
    SELECT DISTINCT customer_id FROM @Table

    OPEN CUR
    FETCH NEXT FROM CUR INTO @customer_id

    DECLARE @CustTable TABLE(
      customer_id INT,
      assetids VARCHAR(MAX)
    )
    DECLARE @CustTableIDS TABLE(
      ID INT IDENTITY(1,1),
      assetids VARCHAR(MAX)
    )

    WHILE @@FETCH_STATUS = 0
    BEGIN
     DECLARE @ConCats AS VARCHAR(MAX)
     SET @ConCats = NULL
     SELECT @ConCats = COALESCE(@ConCats + ',' + CAST(asset_id AS VARCHAR(50)), CAST(asset_id AS VARCHAR(50)))
     FROM @Table
     WHERE customer_id = @customer_id
        ORDER BY asset_id

     INSERT INTO @CustTable SELECT @customer_id, @ConCats
     IF NOT EXISTS(SELECT 1 FROM @CustTableIDS WHERE assetids = @ConCats) 
     BEGIN
      INSERT INTO @CustTableIDS SELECT @ConCats
     END

     FETCH NEXT FROM CUR INTO @customer_id
    END

    CLOSE CUR
    DEALLOCATE CUR

    SELECT  * 
    FROM    @Table

    SELECT  ctid.ID,
      ct.customer_id,
      t.asset_id 
    FROM    @CustTableIDS ctid INNER JOIN
      @CustTable ct ON ctid.assetids = ct.assetids INNER JOIN
      @Table t ON ct.customer_id = t.customer_id

Answer 2

WITH    rows (customer_id, asset_id) AS
        (
        SELECT  1, 1
        UNION ALL
        SELECT  1, 2
        UNION ALL
        SELECT  2, 1
        UNION ALL
        SELECT  2, 2
        UNION ALL
        SELECT  3, 1
        UNION ALL
        SELECT  3, 2
        UNION ALL
        SELECT  3, 3
        UNION ALL
        SELECT  4, 1
        UNION ALL
        SELECT  4, 2
        )
SELECT  (
        SELECT  CAST(asset_id AS VARCHAR(10)) + '.' AS [text()]
        FROM    rows ri
        WHERE   ri.customer_id = ro.customer_id
        ORDER BY
                asset_id
        FOR XML PATH('')
        ) AS assets,
        ra.*
FROM    (
        SELECT  DISTINCT customer_id
        FROM    rows
        ) ro
JOIN    rows ra
ON      ra.customer_id = ro.customer_id