- A+

Category：Languages

I'm looking for some idea to 'merge' multiple rows into single one

Lets say I have such table:

`ID | A | B | C | D | ____________________________ 1 | x | y |null | z | 2 |null | z |null | x | 3 | p | w | a |null | 4 | o |null | k |null | `

Now I need to apply changes from all rows with lower ID to each row. The first row is a base so it should look that:

`1 | x | y |null | z | `

Now, for the second row, I need to take the first row and apply changes (if a column is not null) from the second. The second row should look like that:

`2 | x | z |null | x | `

Third row - take the first row, apply changes from second and from third:

`3 | p | w | a | x | `

Fourth row - take the first row, apply changes from second, third and forth:

`4 | o | w | k | x | `

So the output should look like that:

`1 | x | y |null | z | 2 | x | z |null | x | 3 | p | w | a | x | 4 | o | w | k | x | `

Is there any Oracle feature that can be used for that?

Use hierachical query:

`CREATE TABLE abcd( id int, A varchar2(10), B varchar2(10), C varchar2(10), D varchar2(10) ); insert all into abcd values(1,'x','y',null,'z') into abcd values(2,null,'z',null,'x') into abcd values(3,'p','w','a',null) into abcd values(4,'o',null,'k',null) select 1 from dual; commit; `

`WITH qq(id, a,b,c,d) AS( SELECT id, a,b,c,d FROM abcd WHERE id = 1 UNION ALL SELECT x.id, coalesce( x.a, qq.a ), coalesce( x.b, qq.b ), coalesce( x.c, qq.c ), coalesce( x.d, qq.d ) FROM qq JOIN abcd x ON x.id = qq.id + 1 ) SELECT * FROM qq; ID A B C D ---------- ---------- ---------- ---------- ---------- 1 x y z 2 x z x 3 p w a x 4 o w k x `