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
| WITH t AS ( SELECT 1 w, 1 ID, 0 flg FROM dual
UNION ALL SELECT 2 w, 1 ID, 1 flg FROM dual
UNION ALL SELECT 3 w, 1 ID, 1 flg FROM dual
UNION ALL SELECT 4 w, 1 ID, 0 flg FROM dual
UNION ALL SELECT 1 w, 2 ID, 1 flg FROM dual
UNION ALL SELECT 2 w, 2 ID, 0 flg FROM dual
UNION ALL SELECT 3 w, 2 ID, 0 flg FROM dual
)
SELECT ID, RPAD(CONCAT, 10, '0') AS result
FROM (
SELECT ID, row_number() OVER (PARTITION BY ID ORDER BY w) AS num,
flg
|| lead(flg, 1) OVER (PARTITION BY ID ORDER BY w)
|| lead(flg, 2) OVER (PARTITION BY ID ORDER BY w)
|| lead(flg, 3) OVER (PARTITION BY ID ORDER BY w)
|| lead(flg, 4) OVER (PARTITION BY ID ORDER BY w)
|| lead(flg, 5) OVER (PARTITION BY ID ORDER BY w)
|| lead(flg, 6) OVER (PARTITION BY ID ORDER BY w)
|| lead(flg, 7) OVER (PARTITION BY ID ORDER BY w)
|| lead(flg, 8) OVER (PARTITION BY ID ORDER BY w)
|| lead(flg, 9) OVER (PARTITION BY ID ORDER BY w) AS CONCAT
FROM t
)
WHERE num = 1
ID RESULT
1 0110000000
2 1000000000 |
Partager