1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
|
WITH EmployeeHierarchy(PRJAP_ID, AFFAIRE, LIB80, AFFAIREPERE, level, fullpath)
AS
(
SELECT PRJAP_ID, AFFAIRE, LIB80, AFFAIREPERE, 0 , CAST(AFFAIRE AS NVARCHAR(200))
FROM PRJAP
WHERE AFFAIREPERE = ' ' and DOS = 998
UNION ALL
SELECT e.PRJAP_ID, e.AFFAIRE, e.LIB80, e.AFFAIREPERE, eh.level + 1, CAST(eh.fullpath + '/' + CAST(e.AFFAIRE AS NVARCHAR(20)) AS NVARCHAR(200))
FROM PRJAP e
INNER JOIN EmployeeHierarchy eh ON e.AFFAIREPERE = eh.AFFAIRE and DOS = 998
)
SELECT PRJAP_ID, REPLICATE('--', level) + ' ' + AFFAIRE + ' ' + LIB80, fullpath FROM EmployeeHierarchy
where fullpath like '%F45%' AND AFFAIREPERE <> ' '
ORDER fullpath |
Partager