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

Question

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,

• RLF: Please edit your question and add details, such as showing the failed self join. That information may help someone resolve the problem.

Answer 1

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

• Nick: Thanks for your response @Alex07 ! The code works for data that is on 3 rows. However there are one or two rows that i hadnt noticed before that are not duplicated. ie only one row of that data exists, how can i modify this to take those into account. ive edited my question to show the result for single rows.
• Nick: I found the answer :). I edited the second and third line of the code, adding an OR clause. Thank you very much for your help on this.