Recursive cross join to get all available combinaisons

;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)

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

Chris Morris-439714 (4/9/2010)

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.

;WITH calculator AS (
SELECT
ColumnNo = 1,
Number = 1,
Store = CAST(' 1' AS VARCHAR(3000))
UNION ALL
SELECT
ColumnNo = CASE WHEN lr.Number = tr.MaxRows
THEN lr.ColumnNo - 1
ELSE lr.ColumnNo + 1 END,
Number = CASE WHEN lr.Number = tr.MaxRows
-- go back one 3-char column position, increment number found there
THEN CAST(RIGHT(LEFT(lr.Store, (lr.ColumnNo - 1) * 3), 3) AS INT) + 1
ELSE lr.Number + 1 END,
Store = CASE WHEN lr.Number = tr.MaxRows
THEN CAST(
LEFT(lr.Store, (lr.ColumnNo - 2) * 3) -- < chop off last two 3-char column positions
+ RIGHT(' ' + LTRIM(STR( -- < add new Number
CAST(RIGHT(LEFT(lr.Store, (lr.ColumnNo - 1) * 3), 3) AS INT) + 1
)), 3)
AS VARCHAR(3000))
ELSE CAST(lr.Store + RIGHT(' ' + LTRIM(STR(lr.Number + 1)), 3) AS VARCHAR(3000)) END

FROM calculator lr
INNER JOIN (SELECT CAST(16 AS INT) AS MaxRows) tr -- < 16 is the number of shipping rows
ON NOT (lr.ColumnNo = 1 AND lr.Number = tr.MaxRows)
)
SELECT *
FROM calculator
--WHERE ColumnNo > 1 AND ColumnNo < 16 -- < 16 is the number of shipping rows
OPTION (MAXRECURSION 0)

-- Maxrows = 16: (65,535 row(s) affected) / 00:00:02
-- Maxrows = 20: (1,048,575 row(s) affected) / 00:00:46
-- Maxrows = 21: (2,097,151 row(s) affected) / 00:01:33
-- Maxrows = 22: (4,194,303 row(s) affected) / 00:03:08