Recursive or CTE query help

Transact-SQL
1

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

3

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

image
image516×536 9.48 KB

4

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
5

YES! this is perfect. thank you.