Oracle sql combine several rows of the same table into one row

join;oracle

I have a table where for each given ID, three rows are generated as shown. The first row is the header row:

+-------+------+-------+------+-------+----+----+----+
| ID    | GRP  | ITDESC| DEN  | NUM   | SU | CS | SW |
+-------+------+-------+------+-------+----+----+----+
| 12345 | MANX | Mango | 1    | 10    | 0  | 0  | 1  |
+-------+------+-------+------+-------+----+----+----+
| 12345 | MANX | Mango | 1    | 200   | 0  | 1  | 0  |
+-------+------+-------+------+-------+----+----+----+
| 12345 | MANX | Mango | 2667 | 10000 | 1  | 0  | 0  |
+-------+------+-------+------+-------+----+----+----+

What im trying to achieve

+-------+------+-------+------+-------+----+-----+----+
| ID    | GRP  | ITDESC| DEN  | NUM   | SU | CS  | SW |
+-------+------+-------+------+-------+----+-----+----+
| 12345 | MANX | Mango | 2667 | 10000 | 1  | 200 | 10 |
+-------+------+-------+------+-------+----+-----+----+

If it helps, if SU is 1, then CS and SW are always 0. If CS is 1 then SU and SW are always 0 and if SW =0 then SU and CS are always 0.

So i would like to return:

  • The first 3 columns followed by
  • The value in the DEN and NUM columns where SU = 1 followed by the value in the SU column
  • The value in the NUM column on the row where CS = 1 followed by
  • The value in the NUM column where SW = 1 or 0 if SW = 0

I have tried googling a way forward and found self joins mentioned. i have tried this and due to my limited understanding i just cant get it to work. I get back many more columns of data back !

I would greatly appreciate some help on this.

cheers

UPDATE

This is the SQL i tried to apply and the result was that each row was duplicated 3 times

select tab_su.id
,      tab_su.grp
,      tab_su."desc"
,      tab_su.den
,      tab_su.num
,      tab_cs.num as cs
,      tab_sw.num as sw
from   tab tab_su
,      tab tab_cs
,      tab tab_sw
where  tab_su.su = 1
and    tab_cs.cs = 1
and    tab_sw.sw = 1
and    tab_su.id = tab_cs.id
and    tab_su.id = tab_sw.id;

UPDATE AFTER RUNNING @ALEX07 CODE – RESULT WHEN ONLY ONE ROW EXISTS

BEFORE

+-------+------+-------+------+-------+----+----+----+
| ID    | GRP  | ITDESC| DEN  | NUM   | SU | CS | SW |
+-------+------+-------+------+-------+----+----+----+   
| 12345 | MANX | Mango | 1    | 8     | 0  | 1  | 0  |
+-------+------+-------+------+-------+----+----+----+

AFTER

+-------+------+-------+------+-------+----+----+----+
| ID    | GRP  | ITDESC| DEN  | NUM   | SU | CS | SW |
+-------+------+-------+------+-------+----+----+----+   
| 12345 | MANX | Mango | NULL | NULL  | 0  | 8  | 0  |
+-------+------+-------+------+-------+----+----+----+

WHAT I WAS EXPECTING – (so, no change expected)

+-------+------+-------+------+-------+----+----+----+
| ID    | GRP  | ITDESC| DEN  | NUM   | SU | CS | SW |
+-------+------+-------+------+-------+----+----+----+   
| 12345 | MANX | Mango | 1    | 8     | 0  | 1  | 0  |
+-------+------+-------+------+-------+----+----+----+

PS: Alex07 code works well when there are 2 or 3 rows of data to be combined. How can the code be edited to ignore data that only spans one row?

ANSWER
I edited the second and third line of Alex07's code to be:

MAX(CASE WHEN SU = 1 OR CS = 1 THEN BUOMDEN END) AS BUOMDEN,
MAX(CASE WHEN SU = 1 OR CS = 1 THEN BUOMNUM END) AS BUOMNUM,

Best Answer

I think this will give you desired resultset : 

select id,grp,itdesc,
MAX(CASE WHEN su=1 THEN DEN END) AS DEN,
MAX(CASE WHEN su=1 THEN NUM END) AS NUM,
MAX(CASE WHEN su=1 THEN SU END) AS SU ,  -- or just  "1 AS SU" 
MAX(CASE WHEN cs=1 THEN NUM END) AS CS,
MAX(CASE 
   WHEN sw=1 THEN NUM  
   WHEN sw=0 THEN 0 
   END) AS SW
FROM tab
GROUP BY 
id,grp,itdesc
Related Question