I've been working on a problem that I can't quite figure out. I've tried different combinations of cross joins, CTEs, windowing functions, etc but could never quite get there. I'm also not wanting to go the dynamic SQL route. Can someone please help?
Given a variable set of grouped values produce all possible combinations vertically (derived group, value)
Additional criteria:
1 - No 2 combinations should have the same set of values, regardless of order. Example: If you already have (1,2) then don't produce (2,1), if (1,2,3) then no (1,3,2),(2,1,3),(2,3,1),(3,1,2),(3,2,1)
2 - Values of the same group should not combine
3 - all values are unique, regardless of group. The only reason for the initial grouping is to apply rule #2
Given the starting groups and values of
1 8
2 7
2 9
3 1
3 6
3 3
produce
1 8
2 7
3 9
4 1
5 6
6 3
7 8
7 7
8 8
8 9
9 8
9 1
10 8
10 6
11 8
11 3
12 7
12 1
13 7
13 6
14 7
14 3
15 9
15 1
16 9
16 6
17 9
17 3
18 8
18 7
18 1
19 8
19 7
19 6
20 8
20 7
20 3
21 8
21 9
21 1
22 8
22 9
22 6
23 8
23 9
23 3
Another way to think about it without the derived grouping and vertical output is this
8
7
9
1
6
3
8 7
8 9
8 1
8 6
8 3
7 1
7 6
7 3
9 1
9 6
9 3
8 7 1
8 7 6
8 7 3
8 9 1
8 9 6
8 9 3
Given a variable set of grouped values produce all possible combinations vertically (derived group, value)
Additional criteria:
1 - No 2 combinations should have the same set of values, regardless of order. Example: If you already have (1,2) then don't produce (2,1), if (1,2,3) then no (1,3,2),(2,1,3),(2,3,1),(3,1,2),(3,2,1)
2 - Values of the same group should not combine
3 - all values are unique, regardless of group. The only reason for the initial grouping is to apply rule #2
Given the starting groups and values of
1 8
2 7
2 9
3 1
3 6
3 3
produce
1 8
2 7
3 9
4 1
5 6
6 3
7 8
7 7
8 8
8 9
9 8
9 1
10 8
10 6
11 8
11 3
12 7
12 1
13 7
13 6
14 7
14 3
15 9
15 1
16 9
16 6
17 9
17 3
18 8
18 7
18 1
19 8
19 7
19 6
20 8
20 7
20 3
21 8
21 9
21 1
22 8
22 9
22 6
23 8
23 9
23 3
Another way to think about it without the derived grouping and vertical output is this
8
7
9
1
6
3
8 7
8 9
8 1
8 6
8 3
7 1
7 6
7 3
9 1
9 6
9 3
8 7 1
8 7 6
8 7 3
8 9 1
8 9 6
8 9 3