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 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
|
;WITH A AS ( --jeu d'essai
SELECT 1 AS ID, 'Pierre' AS Nom_Personne
UNION ALL
SELECT 2, 'Marie'
)
,B AS ( --jeu d'essai
SELECT 1 AS ID, 'Foot' AS Club, 10 AS Prix
UNION ALL
SELECT 2, 'Tennis', 20
UNION ALL
SELECT 3, 'badminton', 55
)
,Link AS ( --jeu d'essai
SELECT 1 AS ID, 1 AS Ref_Personne, 1 AS Ref_Club
UNION ALL
SELECT 2,1,2
UNION ALL
SELECT 3, 2, 2
UNION ALL
SELECT 4, 1, 3
)
, LinkRN AS (
SELECT
ID,
Ref_Personne,
Ref_Club,
ROW_NUMBER() OVER (PARTITION BY Ref_Personne ORDER BY Ref_Club) AS RN,
COUNT(*) OVER (PARTITION BY Ref_Personne) AS CNT
FROM Link
)
, CTE AS (
SELECT
Ref_Personne,
CAST(B.Club AS VARCHAR(8000)) AS Club,
B.Prix,
1 AS Rang,
CNT AS Last
FROM LinkRN L
INNER JOIN B
ON L.Ref_Club = B.ID
WHERE RN = 1
UNION ALL
SELECT
CTE.Ref_Personne,
CASE
WHEN L.RN = L.CNT
THEN CTE.Club + ' et ' + B.Club
ELSE CTE.Club + ', ' + B.Club
END,
CTE.Prix + B.Prix,
Rang + 1,
CNT
FROM CTE
INNER JOIN LinkRN L
ON L.Ref_Personne = CTE.Ref_Personne
AND L.RN = Rang + 1
INNER JOIN B
ON L.Ref_Club = B.ID
)
SELECT A.Nom_Personne, CTE.Club, Prix
FROM A
LEFT OUTER JOIN CTE
ON A.ID = CTE.Ref_Personne
WHERE Rang = Last
ORDER BY Nom_Personne |
Partager