Garadin (4/8/2010)
Chris Morris-439714 (4/8/2010)
Ninja (Remi?)Sympathies mate. How much leeway do you have with the db? Can you for instance sneak in a trigger here and there, and maybe a link table?
It's an interesting challenge as you and others have pointed out, so I'm fiddling with a recursive CTE to see if it can do anything useful.
The problem I kept running into with the recursive CTE is the 'missing in the middle' number. Sequences for combos up to 4 combos aren't a problem, once you hit 5 and can have to generate stuff like '02,05' '02,03,05' etc, it falls apart.
Although, in typing that I might have just thought of way to fix that problem. Keyword being "might" =).
Here's a rCTE which works for up to 9 rows. Changing it to work with any number of rows wouldn't be too hard but would make it almost unreadable, and probably pig-slow too. Up to 99 rows would be easy.
;WITH sourcedata AS (SELECT CAST(9 AS INT) AS MaxRows)
, calculator AS (
SELECT
LastColumn = 1,
LastNumber = 1,
Store = CAST(',1' AS VARCHAR(20))
FROM sourcedata
UNION ALL
SELECT
LastColumn = CASE
WHEN lr.LastColumn = tr.MaxRows OR lr.LastNumber = tr.MaxRows THEN lr.LastColumn - 1
ELSE lr.LastColumn + 1 END,
LastNumber = CASE WHEN lr.LastNumber = tr.MaxRows
-- go back one column, fetch number, increment
THEN CAST(SUBSTRING(lr.Store, (lr.LastColumn - 1)*2, 1) AS INT) + 1
ELSE lr.LastNumber + 1 END,
Store = CASE
WHEN lr.LastNumber = tr.MaxRows
THEN CAST(
LEFT(lr.Store, (lr.LastColumn - 2)*2) + ',' + LTRIM(STR(
CAST(SUBSTRING(lr.Store, (lr.LastColumn - 1)*2, 1) AS INT) + 1
))
AS VARCHAR(20))
ELSE CAST(lr.Store + ',' + LTRIM(STR(lr.LastNumber + 1)) AS VARCHAR(20)) END
FROM calculator lr
INNER JOIN sourcedata tr
ON NOT (lr.LastColumn = 1 AND lr.LastNumber = tr.MaxRows)
) SELECT * FROM calculator OPTION (MAXRECURSION 0)
It probably doesn't have any use in context - sorry Ninja - but it's interesting as an example of the stuff you can do with rCTE's, such as generating rows as and when you feel like it, and performing calculations on "adjacent" rows.
Here's some results:
LastColumn LastNumber Store
----------- ----------- --------------------
1 1 ,1
2 2 ,1,2
3 3 ,1,2,3
4 4 ,1,2,3,4
5 5 ,1,2,3,4,5
6 6 ,1,2,3,4,5,6
7 7 ,1,2,3,4,5,6,7
8 8 ,1,2,3,4,5,6,7,8
9 9 ,1,2,3,4,5,6,7,8,9
8 9 ,1,2,3,4,5,6,7,9
7 8 ,1,2,3,4,5,6,8
8 9 ,1,2,3,4,5,6,8,9
7 9 ,1,2,3,4,5,6,9
6 7 ,1,2,3,4,5,7
7 8 ,1,2,3,4,5,7,8
8 9 ,1,2,3,4,5,7,8,9
7 9 ,1,2,3,4,5,7,9
6 8 ,1,2,3,4,5,8
7 9 ,1,2,3,4,5,8,9
6 9 ,1,2,3,4,5,9
5 6 ,1,2,3,4,6
Oh cr@p - Seth, let me know if you're still considering setting up a challenge and I'll edit this out.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden