Generate unique id based on criteria from multiple rows

Question

I've got the following scenario, I've got a table like this

| pid | itemId |
|-----|--------|
|123  | item1  |
|123  | item2  |
|234  | item1  |
|234  | item3  |
|456  | item1  |
|456  | item2  |
|567  | item1  |
|567  | item2  |
|567  | item3  |

I need to get a unique integer id based on the unique combination of items. So if two persons have chosen the same items, the unique combination should be the same number.

For example:

| pid | itemId | comb |
|-----|--------|------|
|123  | item1  |  1   |
|123  | item2  |      |
|234  | item1  |  2   |
|234  | item3  |      |
|456  | item1  |  1   |
|456  | item2  |      |
|567  | item1  |  3   |
|567  | item2  |      |
|567  | item3  |      |

The number of possible combinations at the moment are around 300. So when filtering it might be easier to filter by just one number in one column than filtering with multiple criteria.

Answer 1

One way to approach this problem is to concatenate all of the item values together for each PID and to assign a value to them using the DENSE_RANK window function. Unfortunately, SQL Server does not make it easy to do string concatenation at an aggregate level until STRING_AGG() in SQL Server vNext. Since you're on SQL Server 2008 I'm going to use the FOR XML path method of string aggregation. An explanation of that method along with other ways of doing it can be found in Grouped Concatenation in SQL Server.

Data prep using your sample data:

CREATE TABLE #X165719 (
PID INTEGER NOT NULL,
ITEMID VARCHAR(10) NOT NULL
);

INSERT INTO #X165719
VALUES 
(123, 'item1'),
(123, 'item2'),
(234, 'item1'),
(234, 'item3'),
(456, 'item1'),
(456, 'item2'),
(567, 'item1'),
(567, 'item2'),
(567, 'item3');

First let's do the string aggregation. One implementation is as follows:

SELECT 
  PID
, ITEMID
, (
    SELECT ',' + SUB.ITEMID
    FROM #X165719 SUB
    WHERE SUB.PID = t1.PID
    ORDER BY SUB.ITEMID
    FOR XML PATH (''), TYPE).value('.', 'varchar(max)') all_items
FROM #X165719 t1

You should pick a delimiter that doesn't appear in your source data if possible. I used a comma. Here is what the result set looks like:

╔═════╦════════╦════════════════════╗
║ PID ║ ITEMID ║     all_items      ║
╠═════╬════════╬════════════════════╣
║ 123 ║ item1  ║ ,item1,item2       ║
║ 123 ║ item2  ║ ,item1,item2       ║
║ 234 ║ item1  ║ ,item1,item3       ║
║ 234 ║ item3  ║ ,item1,item3       ║
║ 456 ║ item1  ║ ,item1,item2       ║
║ 456 ║ item2  ║ ,item1,item2       ║
║ 567 ║ item1  ║ ,item1,item2,item3 ║
║ 567 ║ item2  ║ ,item1,item2,item3 ║
║ 567 ║ item3  ║ ,item1,item2,item3 ║
╚═════╩════════╩════════════════════╝

Now we need to assign a different number for each unique value in the all_items column. One way to accomplish this is with the DENSE_RANK function. Quoting from BOL:

Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

The final query is:

SELECT 
  t2.PID
, t2.ITEMID
, DENSE_RANK() OVER (ORDER BY all_items) COMB
FROM
(
    SELECT 
      PID
    , ITEMID
    , (
        SELECT ',' + SUB.ITEMID
        FROM #X165719 SUB
        WHERE SUB.PID = t1.PID
        ORDER BY SUB.ITEMID
        FOR XML PATH (''), TYPE).value('.', 'varchar(max)') all_items
    FROM #X165719 t1
) t2;

The final result set is:

╔═════╦════════╦══════╗
║ PID ║ ITEMID ║ COMB ║
╠═════╬════════╬══════╣
║ 123 ║ item1  ║    1 ║
║ 123 ║ item2  ║    1 ║
║ 456 ║ item1  ║    1 ║
║ 456 ║ item2  ║    1 ║
║ 567 ║ item1  ║    2 ║
║ 567 ║ item2  ║    2 ║
║ 567 ║ item3  ║    2 ║
║ 234 ║ item1  ║    3 ║
║ 234 ║ item3  ║    3 ║
╚═════╩════════╩══════╝

Answer 2

this is a start - this is groups that don't match 100%

select t1.pID, t2.pID, t1.itemID 
from table t1 
full outer join table t2
 on t1.pID < t2.pID 
and t1.itemID = t2.itemID 
order by t1.pID, t2.pID, t1.itemID  
where t1.pID is null or t2.pID is null

this is more - still not done

with cte1 as 
( select pID, count(*) as cnt 
  from table 
  group by pID 
) 
, cte2 as 
( select t1.pID as left, t2.pID as right, count(*) as cnt 
    from table t1 
    join table t2
     on t1.pID < t2.pID 
    and t1.itemID = t2.itemID 
  group by t1.pID, t2.pID 
)
, cte3 as 
( select comb.* 
    from cte1 as letft 
    join cte2 as comb
      on left.pID = comb.left 
     and left.cnt = comb.cnt
    join cte1 as right  
      on right.pID = comb.right 
     and right.cnt = comb.cnt  
)

problem here is if 1 matches 3 and 5 will get
1, 3
1, 5
3, 5

, cte4 as 
( select unq.left, unq.right 
    from cte3 as unq 
    where not exists ( select 1 from cte3 cpy where cpy.lft = unq.right )
)

cte4.left is the groups - now need to number them

, cte5 as 
( select cte4.left  
       , row_number() over ( order by unq.left ) as rn
    from cte4 as unq 
    group by cte4.left
)

I think this is it but I am not sure - there has to be a slicker way

select base.pID, base.itemID, cte5.rn 
  from table base 
  join cte5 
    on cte5.left = base.itemID 
union 
select cpy.pID, cpy.itemID, cte5.rn 
  from table cpy 
  join cte4 
    on cte4.right = cpy.itemID 
  join cte5 
    on cte5.left = cte4.left  
 order by pID, itemID