By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2017 (RTM-CU31-GDR) (KB5029376) - 14.0.3465.1 (X64) Jul 30 2023 15:31:58 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Linux (Rocky Linux 8.8 (Green Obsidian)) |
CREATE TABLE dbo.Example(GroupKey int NOT NULL, RecordKey varchar(12) NOT NULL);
ALTER TABLE dbo.Example
ADD CONSTRAINT iExample PRIMARY KEY CLUSTERED (GroupKey ASC, RecordKey ASC);
INSERT INTO dbo.Example(GroupKey, RecordKey)
VALUES
(1, 'Archimedes'),
(1, 'Newton'),
(1, 'Euler'),
(2, 'Euler'),
(2, 'Gauss'),
(3, 'Gauss'),
(3, 'Poincaré'),
---
(4, 'Ramanujan'),
---
(5, 'Neumann'),
(5, 'Grothendieck'),
(6, 'Grothendieck'),
(6, 'Tao');
12 rows affected
WITH rCTE AS
(
-- Anchor
SELECT
GroupKey, RecordKey,
CAST('|' + CAST(GroupKey AS VARCHAR(10)) + '|' AS VARCHAR(100)) AS GroupKeys,
CAST('|' + CAST(RecordKey AS VARCHAR(10)) + '|' AS VARCHAR(100)) AS RecordKeys,
1 AS lvl
FROM Example
UNION ALL
-- Recursive
SELECT
e.GroupKey, e.RecordKey,
CASE WHEN r.GroupKeys NOT LIKE '%|' + CAST(e.GroupKey AS VARCHAR(10)) + '|%'
THEN CAST(r.GroupKeys + CAST(e.GroupKey AS VARCHAR(10)) + '|' AS VARCHAR(100))
ELSE r.GroupKeys
END,
CASE WHEN r.RecordKeys NOT LIKE '%|' + CAST(e.RecordKey AS VARCHAR(10)) + '|%'
THEN CAST(r.RecordKeys + CAST(e.RecordKey AS VARCHAR(10)) + '|' AS VARCHAR(100))
ELSE r.RecordKeys
END,
r.lvl + 1
FROM rCTE AS r
JOIN Example AS e
ON e.RecordKey = r.RecordKey
AND r.GroupKeys NOT LIKE '%|' + CAST(e.GroupKey AS VARCHAR(10)) + '|%'
--
OR e.GroupKey = r.GroupKey
AND r.RecordKeys NOT LIKE '%|' + CAST(e.RecordKey AS VARCHAR(10)) + '|%'
)
SELECT
ROW_NUMBER() OVER (ORDER BY GroupKeys) AS rn,
GroupKeys, RecordKeys
FROM rCTE AS c
| rn | GroupKeys | RecordKeys |
|---|---|---|
| 1 | |1|2|3| | |Newton|Archimedes|Euler|Gauss|Poincaré| |
| 2 | |4| | |Ramanujan| |
| 3 | |5|6| | |Neumann|Grothendie|Tao| |