I have a dataset that looks like this

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

For this example, the output would look like

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

CodePudding user response：

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 

CodePudding user response：

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;

CodePudding user response：

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

CodePudding user response：

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