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 42 43 44 45 46 47 48 49 50 51 52 53 54 55
| WITH Ordered AS (
select
id
, v1
, v2
-- Si vos id sont consécutifs utilisez :
, id AS Row
/* Sinon utilisez :
, ROW_NUMBER() OVER (ORDER BY id ASC) AS Row
Mais ce sera beaucoups plus lent */
from tTest
)
, CTE AS (
select
id
, v1
, v2
, Row
, 0 AS Done
from Ordered
UNION ALL
select
id
, Y.v1
, Y.v2
, Y.Row
, X.done
from CTE
cross apply (
SELECT case when v1 is not null and v2 is not null then 1 else 0 end
) as X(done)
CROSS APPLY (
SELECT
CTE.v1
, CTE.v2
, CTE.Row
WHERE X.done = 1
UNION ALL
SELECT
ISNULL(CTE.v1, O.v1)
, ISNULL(CTE.v2, O.v2)
, O.Row
FROM Ordered AS O
WHERE X.done = 0
AND O.Row = CTE.Row - 1
) AS Y
where CTE.done = 0
)
SELECT
id
, v1
, v2
FROM CTE
WHERE Done = 1
ORDER BY id ASC |
Partager