Identifying sets of linked IDs in SQL Server

Question

I have a simple table that looks like this:

ClientID    ItemID
1           1
1           2
1           3
2           1
2           2
3           3
4           3
5           1
5           2
5           4
5           5

where both columns combine to be the primary key. I am now tasked with identifying all the unique sets of ItemIDs assigned to ClientIDs. So in my example, the sets would be:

ItemIDs 1,2,3 (used by ClientID 1)
ItemIDs 1,2 (used by ClientID 2)
ItemIDs 3 (used by ClientIDs 3 and 4)
ItemIDs 1,2,4,5 (used by ClientID 5)

Ideally the output would be two tables:

SetID    ItemID
1        1
1        2
1        3
2        1
2        2
3        3
4        1
4        2
4        4
4        5

ClientID    SetID
1           1
2           2
3           3
4           3
5           4

where SetID would be a new field for use elsewhere.

Currently the way I have of identifying the unique sets involves using a cursor to build a string of the ordered ItemIDs for each ClientID, then comparing the output to get the unique strings, and finally parsing it back. It was quick enough to write but feels horrible.

I'm sure there must be a better way than this. Any ideas?

It is hard to understand what you need here – t-clausen.dk Jul 28 '11 at 15:54 — t-clausen.dk, Jul 28 '11 at 15:54

score 1 · Accepted Answer · edited Jan 18 '21 at 12:30

-- Table to hold test data
declare @T table
(
  ClientID int,
  ItemID int
)

insert into @T values
(1, 1),(1, 2),(1, 3),
(2, 1),(2, 2),
(3, 3),(4, 3),
(5, 1),(5, 2),(5, 4),(5, 5)


-- Temp table that will hold the generated set's
declare @Tmp table
(
  ClientID int,
  ItemIDSet varchar(max),
  SetID int
)

-- Query the sets using rank() over a comma separated ItemIDSet
insert into @Tmp
select ClientID,
       ItemIDSet,
       rank() over(order by ItemIDSet) as SetID
from (
      select T1.ClientID,
             stuff((select ','+cast(T2.ItemID as varchar(10))
                    from @T as T2
                    where T1.ClientID = T2.ClientID
                    order by T2.ItemID
                    for xml path('')), 1, 1, '') as ItemIDSet
      from @T as T1
      group by T1.ClientID
     ) as T

-- Get ClientID and SetID from @Tmp
select ClientID, 
       SetID
from @Tmp
order by ClientID

-- Get SetID and ItemID from @Tmp
select SetID,
       T3.N.value('.', 'int') as ItemID
from ( 
       select distinct
              SetID,
              '<i>'+replace(ItemIDSet, ',', '</i><i>')+'</i>' as ItemIDSet
       from @Tmp
     ) as T1
  cross apply 
     ( 
       select cast(T1.ItemIDSet as xml) as ItemIDSet
     ) as T2
  cross apply T2.ItemIDSet.nodes('i') as T3(N)

Result:

ClientID    SetID
----------- -----------
1           2
2           1
3           4
4           4
5           3

SetID       ItemID
----------- -----------
1           1
1           2
2           1
2           2
2           3
3           1
3           2
3           4
3           5
4           3

The values of SetID's is not exactly the same as in the output you have provided but I don't think that would be a big issue. The SetID's are generated from the rank function rank() over(order by ItemIDSet) ordered by ItemIDSet.

Take it for a spin.

Worked like a charm :) I always forget about FOR XML PATH – MartW Aug 01 '11 at 16:11 — MartW, Aug 01 '11 at 16:11

Identifying sets of linked IDs in SQL Server

1 Answers1