Gerar ID exclusivo com base em critérios de várias linhas

Uma maneira de abordar esse problema é concatenar todos os valores de item para cada um PIDe atribuir um valor a eles usando a função de janela DENSE_RANK . Infelizmente, o SQL Server não facilita a concatenação de strings em um nível agregado até STRING_AGG() no SQL Server vNext. Como você está no SQL Server 2008, vou usar o FOR XMLmétodo de caminho de agregação de strings. Uma explicação desse método, juntamente com outras maneiras de fazê-lo, pode ser encontrada em Grouped Concatenation in SQL Server .

Preparação de dados usando seus dados de amostra:

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');

Primeiro vamos fazer a agregação de strings. Uma implementação é a seguinte:

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

Você deve escolher um delimitador que não apareça em seus dados de origem, se possível. Eu usei uma vírgula. Aqui está a aparência do conjunto de resultados:

╔═════╦════════╦════════════════════╗
║ 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 ║
╚═════╩════════╩════════════════════╝

Agora precisamos atribuir um número diferente para cada valor exclusivo na all_itemscoluna. Uma maneira de fazer isso é com a DENSE_RANKfunção. Citando o BOL:

Retorna a classificação das linhas na partição de um conjunto de resultados, sem lacunas na classificação. A classificação de uma linha é um mais o número de classificações distintas que vêm antes da linha em questão.

A última consulta é:

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;

O conjunto de resultados final é:

╔═════╦════════╦══════╗
║ 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 ║
╚═════╩════════╩══════╝

isso é um começo - são grupos que não correspondem 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

isso é mais - ainda não feito

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  
)

problema aqui é se 1 combina com 3 e 5 vai dar
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 são os grupos - agora precisa numerá-los

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

Acho que é isso, mas não tenho certeza - tem que haver uma maneira mais esperta

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