Solution to the Matching sets problem

This is in response to my earlier matching sets post.

I actually figured this out not too long after the original post, but then life happened (and the project that needed this was fundamentally re-defined, making the question obsolete).

So today I was doing some clean-up work and ran into to the code that I was able to use to solve the problem and realized that I never posted it.

The only real solution I came up with is a fairly brute force solution: Take all of my groups and render them as ordered lists, then take the service codes from each work order and render them into ordered lists as well, and finally see which work order matched up to which group.

Broken down:
This collapses the group definition.
SELECT
      WONum
    , STUFF((
             SELECT
             '| ' + dbo.WOs.ItemCode
             FROM
              dbo.WOs
             WHERE
              W.WONum = WOs.WONum
              AND ItemCode IN (
                               SELECT DISTINCT
                                  ItemCode
                               FROM
                                dbo.QualifierGroups )
                               ORDER BY
                                 ItemCode
                               FOR
                               XML PATH('')
                                ), 1, 2, '') IGroup
FROM
 dbo.WOs W
WHERE
  W.ItemCode IN ( SELECT DISTINCT
                        ItemCode
                  FROM
                  dbo.QualifierGroups )
GROUP BY
  WONum

Which results in:
 
The second half does the same thing for the groups:
SELECT
     G.GroupId
   , STUFF((
            SELECT
             '| ' + ItemCode
            FROM
              dbo.QualifierGroups
            WHERE
              GroupId = G.GroupId
            ORDER BY
              ItemCode
            FOR
              XML PATH('')
              ), 1, 2, '') IGroup
 FROM
  dbo.QualifierGroups G
 GROUP BY
  G.GroupId
Which then results in:




When put all together we get:


WITH    WOGroup
          AS (
               SELECT
                    WONum
                  , STUFF((
                            SELECT
                                    '| ' + dbo.WOs.ItemCode
                                FROM
                                    dbo.WOs
                                WHERE
                                    W.WONum = WOs.WONum
                                    AND ItemCode IN (
                                    SELECT DISTINCT
                                            ItemCode
                                        FROM
                                            dbo.QualifierGroups )
                                ORDER BY
                                    ItemCode
                          FOR
                            XML PATH('')
                          ), 1, 2, '') IGroup
                FROM
                    dbo.WOs W
                WHERE
                    W.ItemCode IN ( SELECT DISTINCT
                                            ItemCode
                                        FROM
                                            dbo.QualifierGroups )
                GROUP BY
                    WONum
             ) ,
       
        GroupCodes
          AS (
               SELECT
                    G.GroupId
                  , STUFF((
                            SELECT
                                    '| ' + ItemCode
                                FROM
                                    dbo.QualifierGroups
                                WHERE
                                    GroupId = G.GroupId
                                ORDER BY
                                    ItemCode
                          FOR
                            XML PATH('')
                          ), 1, 2, '') IGroup
                FROM
                    dbo.QualifierGroups G
                GROUP BY
                    G.GroupId
             )
SELECT
    w.WONum
    , ISNULL(gp.BonusPts, 0) BonusPts
    , g.GroupId
    , gp.GroupName
FROM
    WOGroup W
LEFT OUTER JOIN (
                    GroupCodes G
                    INNER JOIN dbo.[Group] GP
                    ON GP.GroupId = G.GroupId
                )
    ON G.IGroup = W.IGroup

So the final result becomes:




 

Note that I did add one more group (F) in addition to the others shown in the original, for testing purposes.

I hope this helps anyone who happens to stumble across it. Enjoy!