T-SQL возвращает все комбинации таблицы

Если вы хотите сгенерировать все комбинации статей, вы можете использовать рекурсивное CTE. Однако трюк состоит в том, чтобы хранить все идентификаторы на каждом уровне, но для этого требуется использовать строку (какого-то рода).

Итак, для статей вы можете сделать:

with a as (
      select distinct article from t
     ),
     cte as (
      select a.article, 1 as lev, convert(varchar(max), article) as grp
      from a
      union all
      select a.article, lev + 1, concat(grp, ':', a.article)
      from cte join
           a
           on a.article > cte.article
     )
select cte.combination_number, s.value
from (select cte.*, row_number() over (order by lev, grp) as combination_number
      from cte
     ) cte cross apply
     string_split(grp, ':') s
order by cte.combination_number, s.value;

Вам также, кажется, нужен один из поставщиков. Вы можете произвольно выбрать поставщика, но я не уверен, какие правила вам нужны для этого.

Здесь db<>рабочий пример.

Обновлено:

Если вы хотите, чтобы пары поставщиков товаров рассматривались как единое целое, но разные товары в каждой комбинации, вы можете настроить приведенный выше запрос:

with a as (
      select distinct article from t
     ),
     cte as (
      select a.article, 1 as lev, convert(varchar(max), article) as grp
      from a
      union all
      select a.article, lev + 1, concat(grp, ':', a.article)
      from cte join
           a
           on a.article > cte.article
     )
select cte.combination_number, s.value
from (select cte.*, row_number() over (order by lev, grp) as combination_number
      from cte
     ) cte cross apply
     string_split(grp, ':') s
order by cte.combination_number, s.value;

Скрипт db<> также имеет это решение.

Чтобы создать этот результат, вам нужно определить две вещи:

• Набор данных с идентификаторами для количества возможных комбинаций.
• Способ определить, когда пара «товар/поставщик» соответствует идентификатору комбинации.

Для того, чтобы выяснить набор комбинаций, вам нужно сначала выяснить количество комбинаций. Для этого вам нужно выяснить размер каждого набора поставщиков для каждого артикула. Получение размера легко достигается с помощью агрегатной функции count, но для того, чтобы вычислить комбинации, мне нужно произведение всех значений, что не так просто сделать. К счастью, в другом SO-вопросе был ответ о том, как это сделать.

Теперь, когда количество комбинаций определено, необходимо сгенерировать идентификаторы. В TSQL нет хорошего способа сделать это. В итоге я использовал простой рекурсивный CTE. У этого есть недостаток, заключающийся в том, что он может создавать только до 32767 комбо. Есть и другие способы получения этих значений, если вам нужно больше.

Чтобы определить, когда пара "товар/поставщик" совпадает с комбинацией, я использую оконную функцию ROW_NUMBER, разбивающую по артикулам и отсортированную по поставщикам, чтобы получить порядковый номер для каждой пары. Затем он использует старый трюк с использованием модуля числа комбинации по порядковому номеру, чтобы определить, отображается ли пара.

По-прежнему существует проблема, заключающаяся в том, что нет гарантии, что избыточные пары не будут объединены в пары. Чтобы решить эту проблему, был добавлен CTE, который вычисляет количество возможных комбинаций, предшествующих артикулу. Намерение состоит в том, чтобы значение в более поздней статье повторялось количество раз для комбинации, прежде чем будет отображаться следующее по порядку. Я назвал это множителем (хотя я делю на него comboId), и это то, что обеспечит отличные результаты.

WITH ComboId AS (
    -- Product from https://stackoverflow.com/questions/3912204/why-is-there-no-product-aggregate-function-in-sql
    SELECT 0 [ComboId], exp (sum (log (SequenceCount))) MaxCombos
    FROM (
        SELECT COUNT(*) SequenceCount
        FROM src 
        GROUP BY Article
    ) x
    UNION ALL
    SELECT ComboId + 1, MaxCombos
    FROM ComboId
    WHERE ComboId + 1 < MaxCombos
)
, Multiplier AS (
    SELECT s.Article
        , COALESCE(exp (sum (log (SequenceCount)) OVER (ORDER BY Article ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)), 1) [Multiplier]
    FROM (
        SELECT Article, COUNT(*) SequenceCount
        FROM src
        GROUP BY Article
    ) s
)
, Sequenced AS (
    SELECT s.Article, s.Supplier, m.Multiplier
        , ROW_NUMBER() OVER (PARTITION BY s.Article ORDER BY s.Supplier) - 1 ArtSupplierSeqNum
        , COUNT(*) OVER (PARTITION BY s.Article) MaxArtSupplierSeqNum
    FROM src s
    INNER JOIN Multiplier m ON m.Article = s.Article
)
SELECT c.ComboId + 1 [ComboId], s.Article, s.Supplier
FROM ComboId c
INNER JOIN Sequenced s ON s.ArtSupplierSeqNum = CAST(c.ComboId / Multiplier as INT) % s.MaxArtSupplierSeqNum
ORDER BY ComboId, Article
OPTION (MAXRECURSION 32767)