How to merge multiple rows into single in Oracle

  • 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          

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: