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
|
WITH
phrases (phrase, id, position)
AS
(
SELECT CAST(dbo.T_ARBO_PRG.PRG_ROOT + dbo.PRG_HC.HC_ANUM AS VARCHAR(max))
+ CASE
WHEN SUBSTRING(dbo.T_ARBO_PRG.PRG_ROOT + dbo.PRG_HC.HC_ANUM, LEN(dbo.T_ARBO_PRG.PRG_ROOT + dbo.PRG_HC.HC_ANUM), 1) = '''' THEN ''
ELSE ' '
END, PRG_HC.HC_ID, PRG_LEVEL
FROM dbo.PRG_HC
INNER JOIN dbo.T_ARBO_PRG ON dbo.PRG_HC.PRG_ID = dbo.T_ARBO_PRG.PRG_ID
INNER JOIN dbo.HC ON dbo.HC.HC_ID = dbo.PRG_HC.HC_ID
WHERE PRG_LEVEL = 0
UNION ALL
SELECT phrase + CAST(dbo.T_ARBO_PRG.PRG_ROOT + suiv.HC_ANUM AS VARCHAR(max))
+ CASE
WHEN SUBSTRING(dbo.T_ARBO_PRG.PRG_ROOT + suiv.HC_ANUM, LEN(dbo.T_ARBO_PRG.PRG_ROOT + suiv.HC_ANUM), 1) = '''' THEN ''
ELSE ' '
END AS PHRASE,
suiv.HC_ID, PRG_LEVEL
FROM dbo.PRG_HC AS suiv
INNER JOIN dbo.T_ARBO_PRG ON suiv.PRG_ID = dbo.T_ARBO_PRG.PRG_ID
INNER JOIN dbo.HC ON dbo.HC.HC_ID = suiv.HC_ID
INNER JOIN phrases
ON suiv.HC_ID = phrases.id
AND T_ARBO_PRG.PRG_LEVEL = phrases.position + 1
),
maxphrase
AS
(
SELECT id, MAX(position) AS maxposition
FROM phrases
GROUP BY id
)
SELECT P.id, RTRIM(phrase) + '.' AS PHRASE
FROM phrases AS P
INNER JOIN maxphrase AS M
ON P.id = M.id
AND P.position = M.maxposition
ORDER BY id |
Partager