Список комбинаций чисел с одинаковой суммой

Это слишком долго для комментариев, поэтому я публикую это как ответ. Я хочу отметить, что это статический пример, но я надеюсь, что его можно легко перевести как динамическое утверждение.

Шаги записываются как комментарии в утверждении:

WITH rcte AS
(
   -- Recursive query to generate all numbers from 1 to 4
   SELECT 0 AS Number
   UNION ALL 
   SELECT Number + 1
   FROM rcte
   WHERE Number < 4
), permutations AS (
   -- All possible permutations with sum equal to 4
   -- There is additional column DuplicateMarker. 
   -- It will be used later, because 0,0,0,4 and 0,4,0,0 are the same
   SELECT 
      t1.Number AS Number1,
      t2.Number AS Number2,
      t3.Number AS Number3,
      t4.Number AS Number4,
      CONCAT(LTRIM(STR(t1.Number)), '.', LTRIM(STR(t2.Number)), '.', LTRIM(STR(t3.Number)), '.', LTRIM(STR(t4.Number))) AS DuplicateMarker
   FROM rcte t1, rcte t2, rcte t3, rcte t4
   WHERE (t1.Number + t2.Number + t3.Number + t4.Number) = 4
), duplicates AS (
   -- Get data with splitted DuplicateMarker column
   SELECT *
   FROM permutations
   CROSS APPLY (SELECT [value] FROM STRING_SPLIT(DuplicateMarker, '.')) t
), results AS (
   -- Get unique combinations
   -- WITHIN GROUP (ORDER BY) will order strings and 0.0.0.4 and 0.4.0.0 will be the same
   SELECT DISTINCT STRING_AGG([value], '.') WITHIN GROUP (ORDER BY [value]) AS ScenarioValue
   FROM duplicates
   GROUP BY Number1, Number2, Number3, Number4
)
SELECT 
   DENSE_RANK() OVER (ORDER BY r.ScenarioValue) AS ScenarioID,
   s.[value]
FROM results r
CROSS APPLY (SELECT [value] FROM STRING_SPLIT(r.ScenarioValue, '.')) s
WHERE [value] <> '0'

Вывод:

ScenarioID  value
1           4
2           1
2           3
3           2
3           2
4           1
4           1
4           2
5           1
5           1
5           1
5           1

Обновление:

Благодаря комментарию @ AndriyM я сделал некоторые изменения и теперь вы можете исключить манипуляции со строками:

WITH rcte AS
(
   -- Recursive query to generate all numbers from 0 to 4
   SELECT 0 AS Number
   UNION ALL 
   SELECT Number + 1
   FROM rcte
   WHERE Number < 4
), combinations AS (
   -- All different combinations with sum equal to 4
   SELECT 
      t1.Number AS Number1,
      t2.Number AS Number2,
      t3.Number AS Number3,
      t4.Number AS Number4,
      ROW_NUMBER() OVER (ORDER BY t1.Number, t2.Number, t3.Number, t4.NUmber) AS ScenarioID
   FROM rcte t1, rcte t2, rcte t3, rcte t4
   WHERE 
      ((t1.Number + t2.Number + t3.Number + t4.Number) = 4) AND
      (t1.Number <= t2.Number) AND
      (t2.Number <= t3.Number) AND 
      (t3.Number <= t4.Number)
)
SELECT c.ScenarioID, v.[value]
FROM combinations c
CROSS APPLY (VALUES (c.NUmber1), (c.Number2), (c.Number3), (c.Number4)) AS v ([value])
WHERE v.[value] > 0

Обновление 2:

Подход с использованием динамического оператора - возможно, не лучший подход, но основан на утверждении из первого обновления: [1110 ]

-- Set your @n value
DECLARE @n int
SET @n = 4

-- Declarations
DECLARE @combinationsSelect nvarchar(max)
DECLARE @combinationsRowNumber nvarchar(max)
DECLARE @combinationsFrom nvarchar(max)
DECLARE @combinationsWhere1 nvarchar(max)
DECLARE @combinationsWhere2 nvarchar(max)
DECLARE @combinationsValues nvarchar(max)

SET @combinationsSelect = N''
SET @combinationsRowNumber = N''
SET @combinationsFrom = N''
SET @combinationsValues = N''
SET @combinationsWhere1 = N''
SET @combinationsWhere2 = N''

-- Generate dynamic parts of the statement
;WITH numbers AS
(
   SELECT 1 AS Number
   UNION ALL 
   SELECT Number + 1
   FROM Numbers
   WHERE Number < @n
)
SELECT 
    @combinationsSelect = @combinationsSelect + N', t' + LTRIM(STR(Number)) + N'.Number AS Number' + LTRIM(STR(Number)),
    @combinationsRowNumber = @combinationsRowNumber + N', t' + LTRIM(STR(Number)) + N'.Number',
    @combinationsValues = @combinationsValues + N', (c.Number' + LTRIM(STR(Number)) + N')',
    @combinationsFrom = @combinationsFrom + N', rcte t' + LTRIM(STR(Number)),
    @combinationsWhere1 = @combinationsWhere1 + N'+ t' + LTRIM(STR(Number)) + N'.Number ',
    @combinationsWhere2 = @combinationsWhere2 + 
        CASE
            WHEN Number = 1 THEN N''
            ELSE N'AND (t' + LTRIM(STR(Number-1)) + N'.Number <= t' +  + LTRIM(STR(Number)) + N'.Number) '
        END
FROM 
    numbers
SET @combinationsSelect = STUFF(@combinationsSelect, 1, 2, N'')
SET @combinationsRowNumber = STUFF(@combinationsRowNumber, 1, 2, N'')
SET @combinationsValues = STUFF(@combinationsValues, 1, 2, N'')
SET @combinationsFrom = STUFF(@combinationsFrom, 1, 2, N'')
SET @combinationsWhere1 = STUFF(@combinationsWhere1, 1, 2, N'')
SET @combinationsWhere2 = STUFF(@combinationsWhere2, 1, 4, N'')

-- Dynamic statement
DECLARE @stm nvarchar(max)
SET @stm =
    N'WITH rcte AS (
       SELECT 0 AS Number
       UNION ALL 
       SELECT Number + 1
       FROM rcte
       WHERE Number < ' + LTRIM(STR(@n)) +
    N'), combinations AS (
       SELECT ' + 
          @combinationsSelect +
          N', ROW_NUMBER() OVER (ORDER BY ' + @combinationsRowNumber + N') AS ScenarioID
       FROM ' + @combinationsFrom +
       N' WHERE ((' + @combinationsWhere1 + N') = ' + LTRIM(STR(@n)) + ') AND ' + @combinationsWhere2 + 
    N')
    SELECT c.ScenarioID, v.[value]
    FROM combinations c
    CROSS APPLY (VALUES ' + @combinationsValues + N') AS v ([value])
    WHERE v.[value] > 0'

-- Execute dynamic statement
EXEC (@stm)