SQL Server Problems with a loop in T-SQL

I have a problem with using a loop to assign an id within a relationship between numbers. I have 6 columns with numbers and I would like each number that has direct or indirect relations to have the same ID to assign them to one group, as shown here:
| number | number1 | number2 | number3 | number4 | number5 | number6 | GroupId |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 11111111111 | 11111111111 | 22222222222 | NULL | NULL | NULL | NULL | 1 |
| 33333333333 | NULL | 22222222222 | 77777777777 | NULL | NULL | NULL | 1 |
| 77777777777 | NULL | NULL | NULL | NULL | NULL | NULL | 1 |
| 77777777777 | NULL | NULL | NULL | NULL | NULL | NULL | 1 |
| 22222222222 | 88888888888 | NULL | NULL | NULL | NULL | NULL | 1 |
| 44444444444 | NULL | NULL | NULL | NULL | NULL | NULL | 2 |
| 55555555555 | 00000000000 | NULL | NULL | NULL | NULL | NULL | 3 |
| 55555555555 | 00000000000 | NULL | NULL | NULL | NULL | NULL | 3 |
| 00000000000 | NULL | NULL | NULL | NULL | NULL | NULL | 3 |

I tried to use standard joins and subqueries, unfortunately without effect. same for the loop. my every approach to creating loops returns me stupidity the same with joins. I expected the result as above and it returns me a completely different Group id

Fitr

CREATE TABLE #PrzypisanieKlientow 
(
    Id INT IDENTITY(1, 1),
    Pesel1 NVARCHAR(11),
    Pesel2 NVARCHAR(11),
    Pesel3 NVARCHAR(11),
    Pesel4 NVARCHAR(11),
    Pesel5 NVARCHAR(11),
    Pesel6 NVARCHAR(11),
    GroupId INT
);

INSERT INTO #PrzypisanieKlientow (Pesel1, Pesel2, Pesel3, Pesel4, Pesel5, Pesel6)
VALUES
    ('11111111111', '11111111111', '22222222222', NULL, NULL, NULL),
    ('33333333333', NULL, '22222222222', '77777777777', NULL, NULL),
    ('77777777777', NULL, NULL, NULL, NULL, NULL),
    ('77777777777', NULL, NULL, NULL, NULL, NULL),
    ('22222222222', '88888888888', NULL, NULL, NULL, NULL),
    ('44444444444', NULL, NULL, NULL, NULL, NULL),
    ('55555555555', '00000000000', NULL, NULL, NULL, NULL),
    ('55555555555', '00000000000', NULL, NULL, NULL, NULL),
    ('00000000000', NULL, NULL, NULL, NULL, NULL);

UPDATE PK
SET GroupId = CTE.GroupId
FROM #PrzypisanieKlientow PK
JOIN (
    SELECT Pesel, DENSE_RANK() OVER (ORDER BY Pesel) AS GroupId
    FROM (
        SELECT Pesel1 AS Pesel FROM #PrzypisanieKlientow WHERE Pesel1 IS NOT NULL
        UNION ALL
        SELECT Pesel2 AS Pesel FROM #PrzypisanieKlientow WHERE Pesel2 IS NOT NULL
        UNION ALL
        SELECT Pesel3 AS Pesel FROM #PrzypisanieKlientow WHERE Pesel3 IS NOT NULL
        UNION ALL
        SELECT Pesel4 AS Pesel FROM #PrzypisanieKlientow WHERE Pesel4 IS NOT NULL
        UNION ALL
        SELECT Pesel5 AS Pesel FROM #PrzypisanieKlientow WHERE Pesel5 IS NOT NULL
        UNION ALL
        SELECT Pesel6 AS Pesel FROM #PrzypisanieKlientow WHERE Pesel6 IS NOT NULL
    ) AS CTE
) CTE
ON PK.Pesel1 = CTE.Pesel OR PK.Pesel2 = CTE.Pesel OR PK.Pesel3 = CTE.Pesel OR PK.Pesel4 = CTE.Pesel OR PK.Pesel5 = CTE.Pesel OR PK.Pesel6 = CTE.Pesel;

Results:

number	number1	number2	number3	number4	number5	number6	GroupId
11111111111	11111111111	22222222222	NULL	NULL	NULL	NULL	2
33333333333	NULL	22222222222	77777777777	NULL	NULL	NULL	3
77777777777	NULL	NULL	NULL	NULL	NULL	NULL	7
77777777777	NULL	NULL	NULL	NULL	NULL	NULL	7
22222222222	88888888888	NULL	NULL	NULL	NULL	NULL	8
44444444444	NULL	NULL	NULL	NULL	NULL	NULL	5
55555555555	00000000000	NULL	NULL	NULL	NULL	NULL	6
55555555555	00000000000	NULL	NULL	NULL	NULL	NULL	6
00000000000	NULL	NULL	NULL	NULL	NULL	NULL	1

You're close. You're updating the GroupId with DENSE_RANK() but you want to assign the same GroupId to Pesel with direct or indirect relations. You should be using the DENSE_RANK() function along with a Common Table Expression (CTE) to create unique groups and then update the main table.

This should do it, though this is untested:

WITH Relationships AS (
    SELECT 
        Pesel1 AS Pesel,
        CASE WHEN Pesel2 IS NOT NULL THEN Pesel2
             WHEN Pesel3 IS NOT NULL THEN Pesel3
             WHEN Pesel4 IS NOT NULL THEN Pesel4
             WHEN Pesel5 IS NOT NULL THEN Pesel5
             WHEN Pesel6 IS NOT NULL THEN Pesel6
        END AS RelatedPesel
    FROM #PrzypisanieKlientow
    WHERE Pesel1 IS NOT NULL
), Grouped AS (
    SELECT DISTINCT Pesel, Pesel AS RootPesel
    FROM Relationships
    UNION ALL
    SELECT r.Pesel, g.RootPesel
    FROM Relationships r
    JOIN Grouped g ON r.RelatedPesel = g.Pesel
), RootGroups AS (
    SELECT DISTINCT Pesel, RootPesel, 
           DENSE_RANK() OVER (ORDER BY RootPesel) AS GroupId
    FROM Grouped
)
UPDATE PK
SET PK.GroupId = RG.GroupId
FROM #PrzypisanieKlientow PK
JOIN RootGroups RG ON PK.Pesel1 = RG.Pesel OR PK.Pesel2 = RG.Pesel OR PK.Pesel3 = RG.Pesel OR PK.Pesel4 = RG.Pesel OR PK.Pesel5 = RG.Pesel OR PK.Pesel6 = RG.Pesel;

SELECT * FROM #PrzypisanieKlientow;

SQL Server Problems with a loop in T-SQL

1条答案

相关问题

热门标签

最新问答