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
| CREATE TABLE Contact
(
IDmembre INT IDENTITY CONSTRAINT PK_Contact PRIMARY KEY,
NomContact VARCHAR(20) NOT NULL
)
GO
CREATE TABLE Club
(
IDClub INT IDENTITY CONSTRAINT PK_Club PRIMARY KEY,
NomClub VARCHAR(20) NOT NULL,
IDmembre INT CONSTRAINT FK_Club_IDmembre FOREIGN KEY (IDmembre) REFERENCES Contact (IDmembre)
)
GO
INSERT INTO dbo.Contact (NomContact) VALUES('BipBipBO')
INSERT INTO dbo.Contact (NomContact) VALUES('ElSuket')
GO
INSERT INTO dbo.Club (NomClub, IDmembre) VALUES ('SQLServer', 1)
INSERT INTO dbo.Club (NomClub, IDmembre) VALUES ('SQLServer', 2)
INSERT INTO dbo.Club (NomClub, IDmembre) VALUES ('C#', 2)
GO
WITH
CTE_LISTE_BRUTE(IDmembre, NomContact, NomClub, Indice) AS
(
SELECT CONTACT.IDmembre,
CONTACT.NomContact,
CLUB.NomClub,
ROW_NUMBER() OVER(PARTITION BY CONTACT.IDmembre ORDER BY CONTACT.IDmembre) AS Indice
FROM dbo.Contact CONTACT
JOIN dbo.Club CLUB ON CLUB.IDmembre = CONTACT.IDmembre
),
CTE_LISTE_CLUB(IDmembre, NomContact, ListeClub, Compteur) AS
(
SELECT IDmembre,
NomContact,
CAST(NomClub AS VARCHAR(8000)) AS ListeClub,
1 AS Compteur
FROM CTE_LISTE_BRUTE
WHERE Indice = 1
UNION ALL
SELECT CTE_LISTE_CLUB.IDmembre,
CTE_LISTE_CLUB.NomContact,
CAST(CTE_LISTE_CLUB.ListeClub + ', ' + CTE_LISTE_BRUTE.NomClub AS VARCHAR(8000)) AS ListeClub,
CTE_LISTE_CLUB.Compteur + 1
FROM CTE_LISTE_CLUB
JOIN CTE_LISTE_BRUTE ON CTE_LISTE_CLUB.IDmembre = CTE_LISTE_BRUTE.IDmembre
WHERE Indice = Compteur + 1
),
CTE_MAX(IDmembre, maxCompteur) AS
(
SELECT IDmembre,
MAX(Compteur) AS maxCompteur
FROM CTE_LISTE_CLUB
GROUP BY IDmembre
)
SELECT CTE_LISTE_CLUB.NomContact, CTE_LISTE_CLUB.ListeClub
FROM CTE_LISTE_CLUB
JOIN CTE_MAX
ON CTE_LISTE_CLUB.Compteur = CTE_MAX.maxCompteur
AND CTE_LISTE_CLUB.IDmembre = CTE_MAX.IDmembre |
Partager