Combinations in SQL Server

Question

Using SQL Server (2008) and given a table with rows as follows:

Id
--
4
7

Using a value for x (e.g. a parameter @x), I want to be able to generate rows with x columns giving the all combinations of the Id values in the table:

For example with x=2, would produce an output with two columns as follows:

4,4
4,7
7,4
7,7

In this case where x=3, the result would be a rows with three columns as following:

4,4,4
4,4,7
4,7,4
4,7,7
7,4,4
7,4,7
7,7,4
7,7,7

The table may contain more or less rows than the 2 rows in the above example, which also depending on the value of x would change the number of combination rows/columns in the output.

E.g. If the table contained:

4
7
9

If x=2, would produce

4,4
4,7
4,9
7,4
7,7
7,9
9,4
9,7
9,9

If x=3, would produce

4,4,4
4,4,7
4,4,9
4,7,4
4,7,7
4,7,9
4,9,4
4,9,7
4,9,9
etc

Thanks

Answer 1

You can do this using a recursive CTE:

with cte as (
      select convert(varchar(max), id) as ids, 1 as cnt
      from t
      union all
      select ids + ',' + convert(varchar(max), id), cnt + 1
      from cte join
           t
           on cte.cnt < @x
     )
select *
from cte
where cnt = @x;

Here is a db<>fiddle.

Note: you need to represent the results as a string, because SQL does not allow you to return a variable number of columns. You could put each value in a separate column, but then you would not be able to use a variable to control the size of the combinations.

Answer 2

Another possible approach is to use dynamic SQL:

-- Table
CREATE TABLE #Numbers (
   Id int
)
INSERT INTO #Numbers
   (Id)
VALUES
   (4),
   (7),
   (9)

-- Declarations
DECLARE @select nvarchar(max)
DECLARE @from nvarchar(max)
DECLARE @stm nvarchar(max)
DECLARE @x int

-- Numbers
SELECT @x = 2

-- Statement generation 
;WITH CounterCTE as (
   SELECT 1 AS Counter
   UNION ALL
   SELECT Counter + 1   
   FROM CounterCTE
   WHERE Counter < @x
)
SELECT 
   @select = (SELECT CONCAT(N',t', Counter, N'.Id') FROM CounterCTE FOR XML PATH('')),
   @from = (SELECT CONCAT(N',#Numbers t', Counter) FROM CounterCTE FOR XML PATH(''))
SET @stm = CONCAT(
   N'SELECT ', 
   STUFF(@select, 1, 1, N''),
   N' FROM ',
   STUFF(@from, 1, 1, N'')
)   

-- Execution
PRINT @stm
EXEC sp_executesql @stm

Output for @x = 2

Id  Id
4   4
7   4
9   4
4   7
7   7
9   7
4   9
7   9
9   9