Идентификаторы/значения группы, которые связаны друг с другом между несколькими столбцами

Я немного поработал над этим и согласен с Кашьяпом - я не могу найти способ сделать это одним утверждением. Вам нужен либо рекурсивный CTE, либо цикл. В настоящее время Synapse не поддерживает рекурсивные CTE, что позволяет использовать цикл для создания желаемого эффекта.

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

Конечно, не единственный вариант, но это сценарий, который я собрал. Это зависит от наличия уникального идентификатора, который будет оставаться неизменным в каждой итерации. Поскольку цикл использует обновления вместо вставок, подготовка данных будет включать в себя вставку данных в вашу новую таблицу без группы, и вы можете создать свой идентификатор в это время, используя автоинкремент или иным образом. Сценарий лучше всего работает со столбцом INT ID, но при необходимости должен работать и с guid.

Итак, процесс по существу таков:

1. Выполните любую начальную подготовку, необходимую для вставки данных в таблицу и создания идентификатора.
2. Присоедините таблицу к самой себе, по одному разу для каждого столбца, который может содержать совпадение.
3. Обновите идентификатор группы, чтобы он был минимальным значением среди идентификаторов и текущих идентификаторов групп этого набора совпадений.
4. Проверьте, не нужно ли нам сделать еще один раунд. Поскольку мы используем минимальный идентификатор в качестве номера группы, будет строка, в которой ID = group ID в каждой группе

CREATE TABLE #testtable
  (
     [id]          INT NOT NULL,
     [col1]        INT NOT NULL,
     [col2]        INT NULL,
     [col3]        INT NULL,
     [groupnumber] INT NULL
  )

INSERT INTO #testtable
            (id,
             col1,
             col2,
             col3)
INSERT INTO #testTable (id, col1, col2, col3) 
    SELECT 1, 1, 5, 33 UNION ALL         -- First
    SELECT 2, 2, null, 45 UNION ALL      -- Second
    SELECT 3, 1, 123, 65 UNION ALL       -- First
    SELECT 4, 3, 123, null UNION ALL     -- First
    SELECT 5, 10, null, 10 UNION ALL      -- Third
    SELECT 6, 5, null, 45 UNION ALL      -- Second
    SELECT 7, 6, 145, 33                -- First


DECLARE @RemainingRows INT,
    @LoopCounter INT, @MaxLoops int             -- To protect against infinite loop
    
SET @RemainingRows = (SELECT COUNT([id]) FROM #testtable)
SET @LoopCounter = 0;
SET @MaxLoops = 10;
    
WHILE( @RemainingRows > 0 
    AND @LoopCounter < @MaxLoops ) 
BEGIN

    WITH combineddata AS 
    (
        SELECT
            id,
            col1,
            col2,
            col3,
            groupnumber
        FROM
            #testtable
    ),
    --Create a set a rows that contains all rows and all possible matches
    matcheddata AS 
    (
        SELECT
            c1.id,
            c1.col1 AS c1col1,
            c1.col2 AS c1col2,
            c1.col3 AS c1col3,
            c1.groupnumber AS groupNumber1,
            c2.id AS RowNum2,
            c2.groupnumber AS groupNumber2,
            c3.id AS RowNum3,
            c3.groupnumber AS groupNumber3,
            c4.id AS RowNum4,
            c4.groupnumber AS groupNumber4 
        FROM
            combineddata c1 
            LEFT JOIN
                combineddata c2 
                ON c1.col1 = c2.col1 
            LEFT JOIN
                combineddata c3 
                ON c1.col2 = c3.col2 
            LEFT JOIN
                combineddata c4 
                ON c1.col3 = c4.col3
    )
    UPDATE #testtable 
    SET
        groupnumber = 
            CASE
                WHEN
                    NEW.groupnumber IS NULL 
                THEN
                    NULL 
                ELSE
                    NEW.groupnumber 
            END
    FROM
        (
            SELECT
                id,
                c1col1,
                c1col2,
                c1col3,
                MIN(groupnumber) AS GroupNumber 
            FROM
                matcheddata CROSS apply (
                SELECT
                    MIN(c) AS GroupNumber 
                FROM (VALUES 
                    (id), 
                    (RowNum2), 
                    (RowNum3), 
                    (RowNum4),
                    (groupNumber1),
                    (groupNumber2),
                    (groupNumber3),
                    (groupNumber4)
                ) AS v (C)
                WHERE
                    c IS NOT NULL) g 
                GROUP BY
                    id,
                    c1col1,
                    c1col2,
                    c1col3
        ) NEW 
    INNER JOIN # testtable 
        ON NEW.id = #testtable.id 
        
    SET
        @LoopCounter = @LoopCounter + 1 
    SET
        @RemainingRows = 
        (
            SELECT
                COUNT(t1.id) 
            FROM
                #testtable t1 
                LEFT JOIN
                    #testtable t2 
                    ON t1.groupnumber = t2.[id] 
            WHERE
                t2.id IS NULL 
                OR t2.id <> t2.groupnumber
        )
        
        PRINT 'Remaining Rows: ' + CAST(@RemainingRows AS VARCHAR) PRINT 'Counter: ' + CAST(@LoopCounter AS VARCHAR);
END

SELECT * FROM #testtable 

IF Object_id('tempdb..#testTable') IS NOT NULL 
    BEGIN
        DROP TABLE # testtable 
    END```