Nick Nick - 4 months ago 14
SQL Question

Oracle SQL - combine data from several rows

i have the following table (Top row is header):

+-------+------+-------+------+-------+----+----+----+
| ID | GRP | DESC | 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 | DESC | DEN | NUM | SU | CS | SW |
+-------+------+-------+------+-------+----+-----+----+
| 12345 | MANX | Mango | 2667 | 10000 | 1 | 200 | 10 |
+-------+------+-------+------+-------+----+-----+----+


There are three rows returned for every ID and the reason is the values contained in the last 3 columns. I want to combine the data for each given ID into one row.

SU, CS and SW cannot all have 1 on the same row.If SU is 1, then CS and SW are always 0. If CS is 1 then SU and SW are always 0 and so on.

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 maybe due to my limited understanding i just cant get it to work. I get back many more rows of data back !

I would greatly appreciate some help on this.

cheers

UPDATE - Result from @vkp code

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

Answer

this worked:

select id,grp,itdesc,
 MAX(CASE WHEN su=1 or cs =1 THEN DEN END) AS DEN,
 MAX(CASE WHEN su=1 or cs =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
Comments