with t as
(
select 3 no1,null no2, null no3, null no4, 4 no5 from dual union all
select null no1,null no2, null no3, null no4, null no5 from dual union all
select null no1,null no2, 2 no3, null no4, null no5 from dual union all
select null no1,null no2, null no3, 5 no4, null no5 from dual union all
select null no1,null no2, null no3, 6 no4, null no5 from dual
)
SELECT
max(DECODE(N1,1,RS2)) no1,
max(DECODE(N1,2,RS2)) no2,
max(DECODE(N1,3,RS2)) no3,
max(DECODE(N1,4,RS2)) no4,
max(DECODE(N1,5,RS2)) no5
from(
SELECT
N2, N1,
LAST_VALUE(RS IGNORE NULLS) OVER(ORDER BY N2,N1) RS2
FROM(
SELECT
N2, N1,
DECODE(N1,1,NO1,2,NO2,3,NO3,4,NO4,5,NO5) RS
FROM (SELECT ROWNUM n1 FROM T)
, (SELECT ROWNUM n2, T.* FROM T)
)
)
group by n2

답글 1등 처음이네요

자료형을 일치시켜주기 위해 샘플데이터에 TO_NUMBER(null) 을 사용했습니다.

WITH t AS
(
SELECT 3 no1, TO_NUMBER(null) no2, TO_NUMBER(null) no3, TO_NUMBER(null) no4, 4 no5 FROM dual
UNION ALL SELECT null, null, null, null, null FROM dual
UNION ALL SELECT null, null, 2, null, null FROM dual
UNION ALL SELECT null, null, null, 5, null FROM dual
UNION ALL SELECT null, null, null, 6, null FROM dual
)
SELECT rn
, COALESCE(no1, no0) no1
, COALESCE(no2, no1, no0) no2
, COALESCE(no3, no2, no1, no0) no3
, COALESCE(no4, no3, no2, no1, no0) no4
, COALESCE(no5, no4, no3, no2, no1, no0) no5
FROM
(
SELECT ROWNUM rn
, no1, no2, no3, no4, no5
, LAST_VALUE(COALESCE(no5, no4, no3, no2, no1) IGNORE NULLS)
OVER(ORDER BY ROWNUM ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) no0
FROM t
)
;

두분다 정답입니다^^

제가 가진 정답은 선모님하고 같은 방식인데 마농님은 copy_t를 사용하지 않고 푸셨네요. 대단합니다.

with t as (
select 3 no1,null no2, null no3, null no4, 4 no5 from dual union all
select null no1,null no2, null no3, null no4, null no5 from dual union all
select null no1,null no2, 2 no3, null no4, null no5 from dual union all
select null no1,null no2, null no3, 5 no4, null no5 from dual union all
select null no1,null no2, null no3, 6 no4, null no5 from dual
)
SELECT NVL(NO1,LAG(COALESCE(NO5,NO4,NO3,NO2,NO1) IGNORE NULLS) OVER(ORDER BY ROWNUM)) AS NO1
,NVL(COALESCE(NO2,NO1),LAG(COALESCE(NO5,NO4,NO3,NO2,NO1) IGNORE NULLS) OVER(ORDER BY ROWNUM)) AS NO2
,NVL(COALESCE(NO3,NO2,NO1),LAG(COALESCE(NO5,NO4,NO3,NO2,NO1) IGNORE NULLS) OVER(ORDER BY ROWNUM)) AS NO3
,NVL(COALESCE(NO4,NO3,NO2,NO1),LAG(COALESCE(NO5,NO4,NO3,NO2,NO1) IGNORE NULLS) OVER(ORDER BY ROWNUM)) AS NO4
,NVL(COALESCE(NO5,NO4,NO3,NO2,NO1),LAG(COALESCE(NO5,NO4,NO3,NO2,NO1) IGNORE NULLS) OVER(ORDER BY ROWNUM)) AS NO5
FROM t

상이님도 정답입니다^^
select 한방으로 해결하셨군요.

11g에선 lag에도 ignore nulls 를 사용할수 있군요.
10g에선 안되네요.

아.. 그렇군요.
여기는 환경이 11g라 당연히 되는줄 알고 있었는데..

그리고 ignore nulls사용하지 않고 한번 풀어보세요^^

ignore nulls 안쓰니...히궁...@ㅅ@;;;;

with t as (
select 3 no1,null no2, null no3, null no4, 4 no5 from dual union all
select null no1,null no2, null no3, null no4, null no5 from dual union all
select null no1,null no2, 2 no3, null no4, null no5 from dual union all
select null no1,null no2, null no3, 5 no4, null no5 from dual union all
select null no1,null no2, null no3, 6 no4, null no5 from dual
)
SELECT NVL(NO1,LAG(TMP) OVER(ORDER BY ROWNUM)) AS NO1
,NVL(COALESCE(NO2,NO1),LAG(TMP) OVER(ORDER BY ROWNUM)) AS NO2
,NVL(COALESCE(NO3,NO2,NO1),LAG(TMP) OVER(ORDER BY ROWNUM)) AS NO3
,NVL(COALESCE(NO4,NO3,NO2,NO1),LAG(TMP) OVER(ORDER BY ROWNUM)) AS NO4
,NVL(COALESCE(NO5,NO4,NO3,NO2,NO1),LAG(TMP) OVER(ORDER BY ROWNUM)) AS NO5
FROM (
SELECT NO1,NO2,NO3,NO4,NO5
,MAX(COALESCE(NO5,NO4,NO3,NO2,NO1) * POWER(10,ROWNUM)) OVER(ORDER BY ROWNUM) /
POWER(10,TO_NUMBER(MAX(NVL2(COALESCE(NO5,NO4,NO3,NO2,NO1),ROWNUM,0)) OVER(ORDER BY ROWNUM)))
AS TMP
FROM t
)

WITH t AS
(
SELECT 3 no1, null no2, null no3, null no4, 4 no5 FROM dual
UNION ALL SELECT null, null, null, null, null FROM dual
UNION ALL SELECT null, null, 2, null, null FROM dual
UNION ALL SELECT null, null, null, 5, null FROM dual
UNION ALL SELECT null, null, null, 6, null FROM dual
)
SELECT rn
, MIN(DECODE(lv,1,no)) no1
, MIN(DECODE(lv,2,no)) no2
, MIN(DECODE(lv,3,no)) no3
, MIN(DECODE(lv,4,no)) no4
, MIN(DECODE(lv,5,no)) no5
FROM
(
SELECT rn, lv
, MIN(no) OVER(PARTITION BY grp) no
FROM
(
SELECT rn, lv
, DECODE(lv,1,no1,2,no2,3,no3,4,no4,5,no5) no
, COUNT(DECODE(lv,1,no1,2,no2,3,no3,4,no4,5,no5)) OVER(ORDER BY rn, lv) grp
FROM (SELECT ROWNUM rn, no1, no2, no3, no4, no5 FROM t)
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <=5)
)
)
GROUP BY rn
ORDER BY rn
;

WITH t AS
(
SELECT 3 no1, TO_NUMBER(null) no2, TO_NUMBER(null) no3, TO_NUMBER(null) no4, 4 no5 FROM dual
UNION ALL SELECT null, null, null, null, null FROM dual
UNION ALL SELECT null, null, 2, null, null FROM dual
UNION ALL SELECT null, null, null, 5, null FROM dual
UNION ALL SELECT null, null, null, 6, null FROM dual
)
SELECT rn
, COALESCE(no1, v1, v2, v3, v4) no1
, COALESCE(no2, no1, v1, v2, v3, v4) no2
, COALESCE(no3, no2, no1, v1, v2, v3, v4) no3
, COALESCE(no4, no3, no2, no1, v1, v2, v3, v4) no4
, COALESCE(no5, no4, no3, no2, no1, v1, v2, v3, v4) no5
FROM
(
SELECT ROWNUM rn
, no1, no2, no3, no4, no5
, LAG(COALESCE(no5, no4, no3, no2, no1), 1) OVER(ORDER BY ROWNUM) v1
, LAG(COALESCE(no5, no4, no3, no2, no1), 2) OVER(ORDER BY ROWNUM) v2
, LAG(COALESCE(no5, no4, no3, no2, no1), 3) OVER(ORDER BY ROWNUM) v3
, LAG(COALESCE(no5, no4, no3, no2, no1), 4) OVER(ORDER BY ROWNUM) v4
FROM t
)
;

WITH t AS
(
SELECT 3 no1, TO_NUMBER(null) no2, TO_NUMBER(null) no3, TO_NUMBER(null) no4, 4 no5 FROM dual
UNION ALL SELECT null, null, null, null, null FROM dual
UNION ALL SELECT null, null, 2, null, null FROM dual
UNION ALL SELECT null, null, null, 5, null FROM dual
UNION ALL SELECT null, null, null, 6, null FROM dual
)
SELECT *
FROM t
MODEL
DIMENSION BY (ROWNUM rn)
MEASURES (no1, no2, no3, no4, no5)
RULES
(
no1[any] = NVL(no1[cv()], COALESCE(no5[cv()-1], no4[cv()-1], no3[cv()-1], no2[cv()-1], no1[cv()-1]))
, no2[any] = NVL(no2[cv()], no1[cv()])
, no3[any] = NVL(no3[cv()], no2[cv()])
, no4[any] = NVL(no4[cv()], no3[cv()])
, no5[any] = NVL(no5[cv()], no4[cv()])
)
;

상이님 마농님 모두 정답니다.^^
마농님은 여러가지 방법으로 풀어주셨네요.
제가 푼 방식은 마농님 첫번째 답변과 유사합니다.

with t as
(
select 3 no1,null no2, null no3, null no4, 4 no5 from dual union all
select null no1,null no2, null no3, null no4, null no5 from dual union all
select null no1,null no2, 2 no3, null no4, null no5 from dual union all
select null no1,null no2, null no3, 5 no4, null no5 from dual union all
select null no1,null no2, null no3, 6 no4, null no5 from dual
)
select
max(decode(rn,1,no)) no1,
max(decode(rn,2,no)) no2,
max(decode(rn,3,no)) no3,
max(decode(rn,4,no)) no4,
max(decode(rn,5,no)) no5
from (
select
num,
rn,
max(no) over (partition by grp) no
from (
select
t.num,
x.rn,
decode(x.rn,1,no1,2,no2,3,no3,4,no4,5,no5) no,
count(decode(x.rn,1,no1,2,no2,3,no3,4,no4,5,no5)) over (order by t.num,x.rn) grp
from (select rownum num,no1,no2,no3,no4,no5 from t) t,
(select rownum rn from dual connect by level <= 5) x
)
)
group by num
order by num

다들 머하시는 분이세요??? 답을 보고도 먼말인지... ㅎㅎ

저두요ㅜㅜ