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,