Generate all combinations of values with set list of values for each character in SQL

I have a dataset that looks like this

Position	Value
1	1
1	2
1	3
2	8
3	5
3	6

And I’d like to generate all combinations of strings with the value at each position.

For this example, the output would look like

Output
185
285
385
186
286
386

The order doesn’t particularly matter.

Note: There can be an arbitrary amount of groups and values per group.

The below SQL sets up the sample input.

Declare @Table Table 
(
    groupId int, 
    value int
)

Insert Into @Table 
    Select 1,1
    union select 1,2
    union select 1,3
    union select 2,8
    union select 3,5
    union select 3,6

Select 
        * 
    From 
        @Table

Answer

You can use a recursive CTE for this, dynamic SQL is not needed

WITH cte AS (
    SELECT
      1 AS Position,
      CAST(Value AS varchar(100)) AS Value
    FROM #YourTable t
    WHERE Position = 1
    
    UNION ALL
    
    SELECT
      cte.Position + 1,
      CAST(cte.Value + t.Value AS varchar(100))
    FROM #YourTable t
    JOIN cte ON cte.Position + 1 = t.Position
)
SELECT *
FROM cte
WHERE cte.Position = (SELECT TOP 1 t2.Position FROM #YourTable t2 ORDER BY t2.Position DESC);

SQL Fiddle

If there are gaps then you a need a more complex solution:

CREATE FUNCTION dbo.GetNextValues (@gtThanPosition int)
RETURNS TABLE AS RETURN

SELECT TOP (1) WITH TIES
  t.Position,
  t.Value
FROM YourTable t
WHERE t.Position > @gtThanPosition OR @gtThanPosition IS NULL
ORDER BY t.Position;

GO

WITH cte AS (
    SELECT
      t.Position,
      CAST(Value AS varchar(100)) AS Value
    FROM dbo.GetNextValues(NULL) t

    UNION ALL
    
    SELECT
      t.Position,
      CAST(cte.Value + t.Value AS varchar(100)) AS Value
    FROM cte
    CROSS APPLY dbo.GetNextValues(cte.Position) t
)
SELECT Value
FROM cte
WHERE cte.Position = (SELECT TOP 1 t2.Position FROM YourTable t2 ORDER BY t2.Position DESC);

SQL Fiddle