Quite a tricky scenario. I have a table as below. Basically I want to get all combinations of ranges from each RangeSet in SQL Server 2012.
Best I show an example of structure and desired output. The problem is the number of RangeSetID's can be dynamic and the number of RangeID's can be dynamic in each range set
RangeID RangeSetID
------------------
1 4
2 4
3 4
4 4
5 2
6 2
7 2
8 2
9 2
10 2
11 1
12 1
13 1
14 1
15 1
16 1
17 3
18 3
19 3
20 3
I need the output to recursively create the below dataset of rates:
1 5 11 17 (first from range4, first from range2, first from range1, first from range3)
1 5 11 18 (first from range4, first from range2, first from range1, second from range3)
1 5 11 19 (first from range4, first from range2, first from range1, third from range3)
1 5 11 20 (first from range4, first from range2, first from range1, fourth from range3)
1 5 12 17 (first from range4, first from range2, second from range1, first from range3)
1 5 12 18 (first from range4, first from range2, second from range1, second from range3)
1 5 12 19
1 5 12 20
And so on until I reach the last RangeID from each RangeSetID and result in
4 10 16 20 (last from range4, last from range2, last from range1, last from range3)
Which will ultimately result in the below where RateID 1 is showing the first result vertically to allow for the dynamic number of RangeSetID's
RateID RangeID
------------------
1 1
1 5
1 11
1 17
2 1
2 5
2 11
2 18
This should result in 11,000 rows (approx). I have tried CROSS JOIN's etc but I cannot get this working at all.
Any geniuses out there please?
Thanks
Aucun commentaire:
Enregistrer un commentaire