I have a dataset as follows:
;WITH CTE AS
( SELECT *
FROM (VALUES
(1, 10, 20, 30)
(2, 10, 21, 31)
(3, 11, 21, 31)
(4, 12, 22, 32)
(5, 13, 23, 33)
(6, 14, 24, 33)
(7, 14, 25, 34)
(8, 15, 26, 36)
) AS MyValues(ID, GroupID1, GroupID2, GroupID3)
)
SELECT *
FROM CTE
How can I generate the following collapsing the individual groups into a single group?
ID | SingleGroupID |
---|---|
1 | 1 |
2 | 1 |
3 | 1 |
4 | 2 |
5 | 3 |
6 | 3 |
7 | 3 |
8 | 4 |
1条答案
按热度按时间cidc1ykv1#
This is a typical graph-walking problem, where you want to build islands of
id
s that have at least one group in common.We can start by unpivoting the groups to build a list of nodes, and generate edges with a self-join (edges connect
id
s that share the same group). We can then recursively traverse the edges, while keeping track of the path we followed until there. The last step is to aggregate by id.So:
fiddle