YUAB0XpW

1CREATE TABLE dbo.Example(GroupKey int NOT NULL, RecordKey varchar(12) NOT NULL);
2ALTER TABLE dbo.Example
3    ADD CONSTRAINT iExample PRIMARY KEY CLUSTERED(GroupKey ASC, RecordKey ASC);
4INSERT INTO dbo.Example(GroupKey, RecordKey)
5VALUES (1, 'Archimedes'), (1, 'Newton'), (1, 'Euler'), (2, 'Euler'), (2, 'Gauss'),
6       (3, 'Gauss'), (3, 'Poincaré'), (4, 'Ramanujan'), (5, 'Neumann'),
7       (5, 'Grothendieck'), (6, 'Grothendieck'), (6, 'Tao');
8	
9SELECT 1 AS SupergroupKey, GroupKey, RecordKey
10FROM dbo.Example
11WHERE GroupKey IN(1, 2, 3)
12UNION ALL
13SELECT 2 AS SupergroupKey, GroupKey, RecordKey
14FROM dbo.Example
15WHERE GroupKey = 4
16UNION ALL
17SELECT 3 AS SupergroupKey, GroupKey, RecordKey
18FROM dbo.Example
19WHERE GroupKey IN(5, 6)
20ORDER BY SupergroupKey ASC, GroupKey ASC, RecordKey ASC;
21	
22DROP TABLE IF EXISTS 
23    dbo.Example;
24
25CREATE TABLE dbo.Example
26(
27    SupergroupKey integer NOT NULL
28        DEFAULT 0, 
29    GroupKey integer NOT NULL, 
30    RecordKey varchar(12) NOT NULL,
31
32    CONSTRAINT iExample 
33    PRIMARY KEY CLUSTERED 
34        (GroupKey ASC, RecordKey ASC),
35
36    CONSTRAINT [IX dbo.Example RecordKey, GroupKey]
37    UNIQUE NONCLUSTERED (RecordKey, GroupKey),
38
39    INDEX [IX dbo.Example SupergroupKey, GroupKey]
40        (SupergroupKey ASC, GroupKey ASC)
41);
42
43INSERT dbo.Example
44    (GroupKey, RecordKey)
45VALUES 
46    (1, 'Archimedes'), 
47    (1, 'Newton'),
48    (1, 'Euler'),
49    (2, 'Euler'),
50    (2, 'Gauss'),
51    (3, 'Gauss'),
52    (3, 'Poincaré'),
53    (4, 'Ramanujan'),
54    (5, 'Neumann'),
55    (5, 'Grothendieck'),
56    (6, 'Grothendieck'),
57    (6, 'Tao');
58	
59-- No execution plans or rows affected messages
60SET NOCOUNT ON;
61SET STATISTICS XML OFF;
62
63-- Reset all supergroups
64UPDATE E
65SET SupergroupKey = 0
66FROM dbo.Example AS E
67    WITH (TABLOCKX)
68WHERE 
69    SupergroupKey != 0;
70
71DECLARE 
72    @CurrentSupergroup integer = 0,
73    @CurrentGroup integer = 0;
74
75WHILE 1 = 1
76BEGIN
77    -- Next super group
78    SET @CurrentSupergroup += 1;
79
80    -- Find the lowest unprocessed group key
81    SELECT 
82        @CurrentGroup = MIN(E.GroupKey)
83    FROM dbo.Example AS E
84    WHERE 
85        E.SupergroupKey = 0;
86
87    -- Exit when no more unprocessed groups
88    IF @CurrentGroup IS NULL BREAK;
89
90    -- Set super group for all records in the current group
91    UPDATE E
92    SET E.SupergroupKey = @CurrentSupergroup
93    FROM dbo.Example AS E 
94    WHERE 
95        E.GroupKey = @CurrentGroup;
96
97    -- Iteratively find all groups for the super group
98    WHILE 1 = 1
99    BEGIN
100        WITH 
101            RecordKeys AS
102            (
103                SELECT DISTINCT
104                    E.RecordKey
105                FROM dbo.Example AS E
106                    WITH (FORCESEEK)
107                WHERE
108                    E.SupergroupKey = @CurrentSupergroup
109            ),
110            GroupKeys AS
111            (
112                SELECT DISTINCT
113                    E.GroupKey
114                FROM RecordKeys AS RK
115                JOIN dbo.Example AS E
116                    WITH (FORCESEEK)
117                    ON E.RecordKey = RK.RecordKey
118            )
119        UPDATE E
120        SET SupergroupKey = @CurrentSupergroup
121        FROM GroupKeys AS GK
122        JOIN dbo.Example AS E
123            WITH (FORCESEEK)
124            ON E.GroupKey = GK.GroupKey
125        WHERE
126            E.SupergroupKey = 0
127        OPTION (FAST 1);
128
129        -- Break when no more related groups found
130        IF @@ROWCOUNT = 0 BREAK;
131    END;
132END;
133
134SELECT
135    E.SupergroupKey,
136    E.GroupKey,
137    E.RecordKey
138FROM dbo.Example AS E;
139	
140WITH rCTE AS 
141(
142    -- Anchor
143    SELECT 
144        GroupKey, RecordKey, 
145        CAST('|' + CAST(GroupKey AS VARCHAR(10)) + '|' AS VARCHAR(100)) AS GroupKeys,
146        CAST('|' + CAST(RecordKey AS VARCHAR(10)) + '|' AS VARCHAR(100)) AS RecordKeys,
147        1 AS lvl
148    FROM Example
149
150    UNION ALL
151
152    -- Recursive
153    SELECT
154        e.GroupKey, e.RecordKey, 
155        CASE WHEN r.GroupKeys NOT LIKE '%|' + CAST(e.GroupKey AS VARCHAR(10)) + '|%'
156            THEN CAST(r.GroupKeys + CAST(e.GroupKey AS VARCHAR(10)) + '|' AS VARCHAR(100))
157            ELSE r.GroupKeys
158        END,
159        CASE WHEN r.RecordKeys NOT LIKE '%|' + CAST(e.RecordKey AS VARCHAR(10)) + '|%'
160            THEN CAST(r.RecordKeys + CAST(e.RecordKey AS VARCHAR(10)) + '|' AS VARCHAR(100))
161            ELSE r.RecordKeys
162        END,
163        r.lvl + 1
164    FROM rCTE AS r
165         JOIN Example AS e
166         ON  e.RecordKey = r.RecordKey
167         AND r.GroupKeys NOT LIKE '%|' + CAST(e.GroupKey AS VARCHAR(10)) + '|%'
168         -- 
169         OR e.GroupKey = r.GroupKey
170         AND r.RecordKeys NOT LIKE '%|' + CAST(e.RecordKey AS VARCHAR(10)) + '|%'
171)
172SELECT 
173    ROW_NUMBER() OVER (ORDER BY GroupKeys) AS SuperGroupKey,
174    GroupKeys, RecordKeys
175FROM rCTE AS c
176WHERE NOT EXISTS
177      ( SELECT 1
178        FROM rCTE AS m
179        WHERE m.lvl > c.lvl
180          AND m.GroupKeys LIKE '%|' + CAST(c.GroupKey AS VARCHAR(10)) + '|%'
181        OR    m.lvl = c.lvl
182          AND ( m.GroupKey > c.GroupKey
183             OR m.GroupKey = c.GroupKey
184             AND m.RecordKeys > c.RecordKeys
185              )
186          AND m.GroupKeys LIKE '%|' + CAST(c.GroupKey AS VARCHAR(10)) + '|%'
187          AND c.GroupKeys LIKE '%|' + CAST(m.GroupKey AS VARCHAR(10)) + '|%'
188      ) 
189OPTION (MAXRECURSION 0) ;