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
|
WITH R (LeCode, Semaine, Jour, Num, Ans, Sequence) AS
(
SELECT LeCode, Semaine, Jour, Num, CAST(Ans AS VARCHAR(512)), 1
FROM V AS x
WHERE (SELECT COUNT(*)
FROM V AS y
WHERE x.LeCode = y.LeCode
AND x.Semaine = y.Semaine
AND x.Jour = y.Jour
AND x.Num > y.Num) < 1
UNION ALL
SELECT x.LeCode, x.Semaine, x.Jour, x.Num
, CAST (CAST(y.Ans AS VARCHAR(512)) + ' / ' + CAST(x.Ans AS VARCHAR(512)) AS VARCHAR(512))
, Sequence + 1
FROM V AS x JOIN R AS y
ON x.LeCode = y.LeCode
AND x.Semaine = y.Semaine
AND x.Jour = y.Jour
AND x.Num > y.Num
)
SELECT x.LeCode, x.Semaine, x.Jour, x.Ans
FROM R AS x JOIN (SELECT LeCode, Semaine, Jour, MAX (Sequence) AS MaxSeq
FROM R
GROUP BY LeCode, Semaine, Jour) AS y
ON x.LeCode = y.LeCode
AND x.Semaine = y.Semaine
AND x.Jour = y.Jour
AND x.Sequence = y.MaxSeq
ORDER BY LeCode, Semaine, Jour ; |
Partager