How to merge multiple rows into single in Oracle

Question

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?

Answer 1

You can use the last_value() function for this, with the ignore nulls option:

last_value(a) ignore nulls over (order by id)

will give you the last not-null value seen in the a column, up to the current ID (using the default window); so you can repeat that for each column you want to 'merge':

select id,
  last_value(a) ignore nulls over (order by id) as a,
  last_value(b) ignore nulls over (order by id) as b,
  last_value(c) ignore nulls over (order by id) as c,
  last_value(d) ignore nulls over (order by id) as d
from your_table
order by id;

With your sample data as a CTE:

with your_table (id,  a,  b,  c,  d) as (
            select 1, 'x', 'y', null, 'z' from dual
  union all select 2, null, 'z', null,'x' from dual
  union all select 3, 'p', 'w', 'a', null from dual
  union all select 4, 'o', null, 'k', null from dual
)
select id,
  last_value(a) ignore nulls over (order by id) as a,
  last_value(b) ignore nulls over (order by id) as b,
  last_value(c) ignore nulls over (order by id) as c,
  last_value(d) ignore nulls over (order by id) as d
from your_table
order by id;

        ID A B C D
---------- - - - -
         1 x y   z
         2 x z   x
         3 p w a x
         4 o w k x