0

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,
1
  • Please edit your question and add details, such as showing the failed self join. That information may help someone resolve the problem.
    – RLF
    Commented Jul 18, 2016 at 14:13

1 Answer 1

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
2
  • 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
    Commented Jul 18, 2016 at 14:55
  • 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.
    – Nick
    Commented Jul 18, 2016 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.