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

Question

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 1

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

Answer 2

You can use a recursive cte:

with cte(c, s) as (
  select 2, cast(value AS varchar(100)) from vals where position = 1
  union all
  select c.c+1, cast(concat(c.s,v.value) as varchar(100)) from cte c join vals v on c.c = v.position
)
select c.s from cte c where c.c = (select top 1 v1.position from vals v1 order by v1.position desc) + 1

Answer 3

I think the real problem here is that you have both an arbitrary number of groups. With a static number of groups you could do something like this:

SELECT CONCAT(YT1.[Value],YT2.[Value],YT3.[Value])
FROM dbo.YourTable YT1
     JOIN dbo.YourTable YT2 ON YT1.Position < YT2.Position
     JOIN dbo.YourTable YT3 ON YT2.Position < YT3.Position;

As a result, one method would be to use a dynamic solution. Note that without details of what version of SQL Server you are using, I am assuming you are using a fully supported version. I also assume you can't have gaps. For example, if position 5 were omitted, but position 6 wasn't, then you don't expect a 0 for the 5 digit (and position 6 would be the 5th digit instead).

This gives you this, which is messy, if I am honest, but does the job:

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
WITH DisctinctPositions AS(
    SELECT DISTINCT
           Position
    FROM dbo.YourTable),
Positions AS(
    SELECT Position,
           LAG(Position) OVER (ORDER BY Position) AS PrevPosition
    FROM DisctinctPositions)
SELECT @SQL = N'SELECT CONCAT(' + STRING_AGG(QUOTENAME(CONCAT(N'YT',P.Position)) + N'.[Value]',',') WITHIN GROUP (ORDER BY P.Position) + N')' + @CRLF +
              N'FROM dbo.YourTable YT1' + @CRLF +
              STRING_AGG(CASE P.Position WHEN 1 THEN NULL ELSE N'     JOIN dbo.YourTable' + QUOTENAME(CONCAT(N'YT',P.Position)) + N' ON ' + QUOTENAME(CONCAT(N'YT',P.PrevPosition)) + N'.Position < ' + QUOTENAME(CONCAT(N'YT',P.Position)) + N'.Position' END, @CRLF) WITHIN GROUP (ORDER BY P.Position)
FROM Positions P;

--PRINT @SQL; Your best friend

EXEC sys.sp_executesql @SQL;

Answer 4

Since you don't know the number of groups, I suspect you'll need to use dynamic SQL.

For example:

DROP TABLE IF EXISTS #TestGroup;

CREATE TABLE #TestGroup
(
    GroupId int,
    Value int
);

INSERT INTO #TestGroup
VALUES
    (1, 1),
    (1, 2),
    (1, 3),
    (2, 8),
    (3, 5),
    (3, 6)
;

DECLARE @MaxGroup int;
SELECT @MaxGroup = MAX(GroupId) FROM #TestGroup;

DECLARE @sql nvarchar(max) = N'SELECT ';

DECLARE @i int = 1;
WHILE @i <= @MaxGroup
BEGIN
    If @i <> 1 SET @sql = @sql + N', ';
    SET @sql = @sql + FORMATMESSAGE(N'T%i.Value As C%i', @i, @i);
    SET @i = @i + 1;
END;

SET @sql = @sql + N' FROM ';
SET @i = 1;
WHILE @i <= @MaxGroup
BEGIN
    If @i <> 1 SET @sql = @sql + N' CROSS JOIN ';
    SET @sql = @sql + FORMATMESSAGE(' (SELECT Value FROM #TestGroup WHERE GroupId = %i) As T%i ', @i, @i);
    SET @i = @i + 1;
END;

PRINT @sql;
EXEC(@sql);

DROP TABLE IF EXISTS #TestGroup;

Output:

SELECT T1.Value As C1, T2.Value As C2, T3.Value As C3 
FROM  (SELECT Value FROM #TestGroup WHERE GroupId = 1) As T1  
CROSS JOIN  (SELECT Value FROM #TestGroup WHERE GroupId = 2) As T2  
CROSS JOIN  (SELECT Value FROM #TestGroup WHERE GroupId = 3) As T3 

C1	C2	C3
1	8	5
2	8	5
3	8	5
1	8	6
2	8	6
3	8	6