1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
|
WITH LaTable(email, fixe, port) AS (
SELECT 'E1', NULL, 'T1'
UNION ALL SELECT 'E1', NULL, 'T1'
UNION ALL SELECT 'E1', NULL, NULL
UNION ALL SELECT 'E20', NULL, 'T10'
UNION ALL SELECT 'E1', NULL, 'T2'
UNION ALL SELECT NULL, 'TE1', 'T2'
UNION ALL SELECT 'E2', NULL, 'T2'
UNION ALL SELECT 'E3', NULL, 'T2'
UNION ALL SELECT 'E2', 'TE1', NULL
UNION ALL SELECT NULL, 'TE1', 'T3'
UNION ALL SELECT NULL, NULL, 'T3'
UNION ALL SELECT 'E10', NULL, 'T10'
UNION ALL SELECT 'E30', NULL, 'T30'
UNION ALL SELECT 'E40', NULL, 'T40'
),
N AS (
SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS num
FROM LaTable
), Rec AS (
SELECT Num, email, fixe , port, Num AS Ori, CAST(CONCAT(';', Num, ';') AS VARCHAR(MAX)) AS chemin
FROM N
UNION ALL
SELECT N.num, N.email, N.fixe, N.port, rec.Ori, CAST(CONCAT(rec.chemin, N.num, ';')AS VARCHAR(MAX))
FROM Rec
INNER JOIN N
ON N.email = Rec.email
OR N.fixe = Rec.fixe
OR N.port = Rec.port
WHERE Rec.chemin NOT LIKE CONCAT('%;',N.num , ';%')
)
SELECT Num, email, fixe, port, MIN(ori) AS Groupe
FROM Rec
GROUP BY Num, email, fixe, port
ORDER BY groupe, num |
Partager