SQLTeam.com | Weblogs | Forums

Recursive or CTE query help

Hi, I have legacy data that I cannot change. Clients are given unique numbers, but when they move to a new service area, they are given a duplicate record with a different GroupID but same ClientID. need a query that will recognize that Mom, Sherry and Sue are all in the same family . That Martha, Dianna, and Sally are all in the same family, and that Loner is in a family all by himself.
CREATE TABLE [dbo].[test_Client](
[client] char NULL,
[GroupID] char NULL
) ON [PRIMARY]
GO
INSERT [dbo].[test_Client] ([client], [GroupID]) VALUES (N'Mom ', N'Group 1 ')
GO
INSERT [dbo].[test_Client] ([client], [GroupID]) VALUES (N'Sue ', N'Group 1 ')
GO
INSERT [dbo].[test_Client] ([client], [GroupID]) VALUES (N'Mom ', N'Group 2 ')
GO
INSERT [dbo].[test_Client] ([client], [GroupID]) VALUES (N'Sherry ', N'Group 2 ')
GO
INSERT [dbo].[test_Client] ([client], [GroupID]) VALUES (N'Sherry ', N'Group 5 ')
GO
INSERT [dbo].[test_Client] ([client], [GroupID]) VALUES (N'Martha ', N'Group 3 ')
GO
INSERT [dbo].[test_Client] ([client], [GroupID]) VALUES (N'Sally ', N'Group 3 ')
GO
INSERT [dbo].[test_Client] ([client], [GroupID]) VALUES (N'Martha ', N'Group 4 ')
GO
INSERT [dbo].[test_Client] ([client], [GroupID]) VALUES (N'Dianna ', N'Group 4 ')
GO
INSERT [dbo].[test_Client] ([client], [GroupID]) VALUES (N'Dianna ', N'Group 6 ')
GO
INSERT [dbo].[test_Client] ([client], [GroupID]) VALUES (N'Loner ', N'Group 10 ')
GO

SQL 2016 is my version and any help is appreciated.
Thank you

Hope this helps .. question is how to write SQL to pick the ones in Red

select 
    * 
from  
   test_Client a 
       join 
   test_Client b on a.GroupID = b.GroupID 
order by 
     a.client 

I think the groups could easily all become linked.
The best I could do with your test data is to create a temp table and use iteration:

WITH GroupJoins
AS
(
	SELECT DISTINCT
		C1.GroupID AS GroupFrom
		,C2.GroupID AS GroupTo
	FROM [dbo].[test_Client] C1
		JOIN [dbo].[test_Client] C2
			ON C1.client = C2.client
	WHERE C1.GroupID <= C2.GroupID
)
SELECT GroupFrom, MAX(GroupTo) AS GroupTo
	,ROW_NUMBER() OVER (ORDER BY GroupFrom) AS LinkGrp
INTO #GroupLinks
FROM GroupJoins
GROUP BY GroupFrom;

WHILE 1=1
BEGIN
	UPDATE L2
	SET LinkGrp = L1.LinkGrp
	FROM #GroupLinks L1
		JOIN #GroupLinks L2
			ON L1.GroupTo = L2.GroupFrom
				AND L1.LinkGrp < L2.LinkGrp;
	IF @@ROWCOUNT = 0
		BREAK;
END

SELECT DISTINCT C.Client, L.LinkGrp
FROM [dbo].[test_Client] C
	JOIN #GroupLinks L
		ON C.GroupID = L.GroupFrom
ORDER BY LinkGrp;
1 Like

YES! this is perfect. thank you.