How to group names which are dependent on other columns

Question

I have a table like below.

CREATE TABLE #Test
(
  CustId INT ,
  CustName VARCHAR(100) ,
  CustHeading VARCHAR(100)
)
INSERT INTO #Test

SELECT '1','john carroll','Heading 1'
UNION ALL
SELECT '1','john carroll','Heading 2' 
UNION ALL
SELECT '2','john c','Heading 1' 
UNION ALL
SELECT '2','john c','Heading 2' 
UNION ALL
SELECT '2','john c','Heading 3' 
UNION ALL
SELECT '3','john lynch','Heading 1' 
UNION ALL
SELECT '4','john carroll lynch','Heading 1' 
UNION ALL
SELECT '4','john carroll lynch','Heading 4' 
UNION ALL
SELECT '4','john carroll lynch','Heading 5' 
UNION ALL
SELECT '5','john c lynch','Heading 1' 
UNION ALL
SELECT '5','john c lynch','Heading 3' 
UNION ALL
SELECT '6','john c l','Heading 11' 
UNION ALL
SELECT '6','john c l','Heading 12' 
UNION ALL
SELECT '7','john c ln','Heading 1' 
UNION ALL
SELECT '7','john c ln','Heading 2' 
UNION ALL
SELECT '2','john c','Heading 11' 
UNION ALL
SELECT '2','john c','Heading 12'

In this, we need to group the customers who are having atleast two matching headings among them.

For example,custID :: 1,2 and 7 are having two matching CustHeading :: Header 1 and Header 2, so they are grouped.CustID :: 2 and 5 having two matching CustHeading :: Header 1 and Header 3,they also can be grouped. Please let me know how to achieve this

without using WHILE loop

Thanks in advance.

Answer 1

Sorry my brain is not working today. I'm sure there is a simpler way to do this but I can think right now. This seems to work fine for me. Let me know if it needs any changes.

CREATE TABLE #Test
(
  CustId INT ,
  CustName VARCHAR(100) ,
  CustHeading VARCHAR(100)
)
INSERT INTO #Test

SELECT '1','john carroll','Heading 1'
UNION ALL
SELECT '1','john carroll','Heading 2' 
UNION ALL
SELECT '2','john c','Heading 1' 
UNION ALL
SELECT '2','john c','Heading 2' 
UNION ALL
SELECT '2','john c','Heading 3' 
UNION ALL
SELECT '3','john lynch','Heading 1' 
UNION ALL
SELECT '4','john carroll lynch','Heading 1' 
UNION ALL
SELECT '4','john carroll lynch','Heading 4' 
UNION ALL
SELECT '4','john carroll lynch','Heading 5' 
UNION ALL
SELECT '5','john c lynch','Heading 1' 
UNION ALL
SELECT '5','john c lynch','Heading 3' 
UNION ALL
SELECT '6','john c l','Heading 11' 
UNION ALL
SELECT '6','john c l','Heading 12' 
UNION ALL
SELECT '7','john c ln','Heading 1' 
UNION ALL
SELECT '7','john c ln','Heading 2' 
UNION ALL
SELECT '2','john c','Heading 11' 
UNION ALL
SELECT '2','john c','Heading 12';


WITH CTE_Heading
AS
(
    SELECT DISTINCT custHeading
    FROM #Test
),
CTE_Paired_Headings
AS
(
    SELECT  A.custHeading AS Head1,
            B.CustHeading AS Head2
    FROM CTE_Heading A
    INNER JOIN CTE_Heading B
    ON A.custHeading < B.custHeading
),
CTE_Matching_Cust
AS
(
SELECT  A.Head1,
        A.Head2,
        B.CustId,
        B.CustName
FROM CTE_Paired_Headings A
INNER JOIN #Test B
    ON A.Head1 = B.CustHeading
    OR A.Head2 = B.CustHeading
GROUP BY A.Head1,A.Head2,B.CustId,B.CustName
HAVING COUNT(*) >= 2
),
CTE_HeadingGroups
AS
(
SELECT  'M' + CAST(ROW_NUMBER() OVER (ORDER BY Head1,Head2) AS VARCHAR(5)) MatchingID,
        Head1,
        Head2
FROM CTE_Matching_Cust
GROUP BY Head1,Head2
HAVING COUNT(*) >= 2
)

SELECT  B.CustId,
        B.CustName,
        A.MatchingID
FROM CTE_HeadingGroups A
INNER JOIN CTE_Matching_Cust B
ON  A.Head1 = B.Head1
    AND A.Head2 = B.Head2
ORDER BY 3,1


DROP TABLE #Test

Results:

CustId      CustName                 MatchingID
-----------------------------------------------
1           john carroll             M1
2           john c                   M1
7           john c ln                M1
2           john c                   M2
5           john c lynch             M2
2           john c                   M3
6           john c l                 M3

Answer 2

SELECT 
DISTINCT
    CASE WHEN c.num = 1 THEN a.CustId
         ELSE a.[_CustId]
    END ,
    CASE WHEN c.num = 1 THEN a.CustName
         ELSE a.[_CustName]
    END ,
    'M' + CAST(DENSE_RANK() OVER ( ORDER BY mx, mn ) AS VARCHAR(100)) AS gr_nbr
FROM
    (
      SELECT
        a.CustId ,
        a.CustName ,
        c.CustId _CustId ,
        c.CustName _CustName,
        MAX(a.CustHeading) mx,
        MIN(a.CustHeading) mn
      FROM
        #Test a
        JOIN #Test c ON c.CustHeading = a.CustHeading
                     AND c.CustId > a.CustId
      GROUP BY
        a.CustId ,
        a.CustName ,
        c.CustId ,
        c.CustName
      HAVING
        MAX(a.CustHeading) <> MIN(a.CustHeading)
    ) a
    JOIN #Test b ON b.CustId = a.[_CustId]
    CROSS JOIN (
                 SELECT
                    1 num
                 UNION ALL
                 SELECT
                    2 num
               ) AS c
ORDER BY
    3 ,
    1