1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
| WITH CHAINE (CAP_CODE, CDM_CODE, CONCATENATION, NbElem) AS
(
SELECT x.CAP_CODE, x.CDM_CODE, CAST(x.CDM_LIB AS VARCHAR(MAX)), 1
FROM APP_DOM AS x JOIN
(
SELECT CAP_CODE, MIN(CDM_CODE) AS CDM_CODE
FROM APP_DOM
GROUP BY CAP_CODE
) AS y
ON x.CAP_CODE = y.CAP_CODE AND x.CDM_CODE = y.CDM_CODE
UNION ALL
SELECT x.CAP_CODE, y.CDM_CODE, CONCATENATION + ' ; ' + y.CDM_LIB, NbElem + 1
FROM CHAINE AS x JOIN APP_DOM AS y ON x.CAP_CODE = y.CAP_CODE
AND x.CDM_CODE < y.CDM_CODE
)
SELECT x.CAP_CODE, CONCATENATION
FROM CHAINE AS x JOIN
(SELECT CAP_CODE, MAX(NbElem) AS MaxNbElem
FROM CHAINE
GROUP BY CAP_CODE) AS y
ON x.CAP_CODE = y.CAP_CODE AND x.NbElem = y.MaxNbElem ; |
Partager