1 2 3 4 5 6 7 8 9 10 11 12
| WITH tree (donnee, id, level, pathstr, nomcontact)
AS (SELECT compte.nom, compte.idcompte, 0, CAST('' AS NVARCHAR(MAX)), contact.nom
FROM compte LEFT JOIN contact on compte.idcompte = contact.idcompte
WHERE compte.idcompte = @Identifiant
UNION ALL
SELECT V.nom, V.idcompte, t.level + 1, t.pathstr + V.nom, contact.nom
FROM compte V INNER JOIN contact on V.idcompte = contact.idcompte
INNER JOIN tree t
ON t.id = V.idcompteparent)
SELECT DISTINCT donnee, id, level, pathstr, nomcontact
FROM tree
ORDER BY pathstr, id |
Partager