Is there an efficient way to match multiples headers that have the same set of details? Here is an example of what I'm asking:

Detail Table:

HeaderId | DetailId
    1          100
    1          101
    2          100
    2          101
    3          101
    3          102
    3          103


I'm looking for a query strategy that will get me all header ids with the same set of detail ids for each.

So in the example, I would want HeaderId 1 and 2 to join together because they have the same two detail records, but 103 would not match because it has a third item in the set.

The strategy I'm taking so far is to use the STUFF technique to create a comma separate string of detail values, checksum that string, then join on the checksum result. It's seems to be working, but I'm not sure how to optimize it. Over a set of about 7000 headers, it returns in about 6-7 seconds.

Here is the query:

with Details as 
(   
    select distinct t2.HeaderId, 
    checksum(stuff((
        select
            ',' + convert(varchar(15), t2.DetailId)
        from
            DetailTable t2
        where
            t2.HeaderId = t2.HeaderId
        for xml path('')
    ),1,1,'')) as ChkSum
from
      DetailTable t1
)
select
    *
from
    Details t1
        join Details t2
            on t2.ChkSum = t1.ChkSum
            and t2.HeaderId &lt;&gt; t1.HeaderId -- To avoid matching the same record


So - is this the right approach? And if it is, how can I optimize? Query plan doesn't have anything jumping out at me. The most weight is given to a table spool. Also, I'm trying to make this a function or proc if that helps.

Edit: I began researching relational division, and I think that is relevant here, but perhaps not in the context I am thinking. To give this more context, here is the business case I am trying to solve.

I have a set of Promotions that can have any number of UPCs in them. I'm trying to find promotions that have exactly the same set of UPCs in them. A lot of the solutions I'm seeing rely on using count(*). So - just some context for anyone looking at this. Thanks!
Here is a way using PIVOT and T-SQL that can work IF you have &lt; 255 unique DetailIDs.  I ran into limitation on CONCAT function (2012+) after writing the thing and stress testing it.  It runs pretty well, &lt;5 seconds on 20k headers over 40k rows, with 254 unique detail keys and a lot of matching.  If your set can fit that limitation, might be worth a look.

DECLARE @sql varchar(MAX)
DECLARE @d varchar(MAX)
SET @d = stuff((
        SELECT ',' + QUOTENAME(DetailId)
        FROM (SELECT DetailId FROM DetailTable GROUP BY DetailId) d
        for xml path('')
    ),1,1,'')
DECLARE @tbl TABLE (H int, D varchar(254))
INSERT INTO @tbl
EXEC(
'SELECT HeaderId,CONCAT('+@d+') Details --'+@d+'
FROM 
(
    SELECT HeaderId, DetailId, 1 o
    FROM DetailTable
) as s
PIVOT
(
    COUNT(o)
    FOR DetailId IN ('+@d+')
) as pvt'
--Possible subquery and JOIN?
)

SELECT t1.H, t2.H H2  --matches
FROM @tbl t1 JOIN @tbl t2 ON t1.D = t2.D AND t1.H &lt; t2.H


You should be able to get around the CONCAT 254 limitation by replacing the select with just @d, wrapping the PIVOT into a subquery, and stuffing out another @dj for a JOIN.