sqloracle-databaseconditional-aggregation

For one employee, In time and Out time should be returned on one line

select 
    compcode, emplcode, attndate, costcode,
    decode(shiftflg, 'I', readtime) INTIME,
    decode(shiftflg, 'O', readtime) OUTTIME
from
    ecatnrec
where  
    emplcode = 'RF025'
order by 
    emplcode;

enter image description here

Solution

  • You can use aggregation:

    select compcode, emplcode, attndate, costcode,
       max(case when shiftflg = 'I' then readtime end) as INTIME,
       max(case when shiftflg = 'O' then readtime end) as OUTTIME
from ecatnrec
where  emplcode = 'RF025'
group by compcode, emplcode, attndate, costcode
order by emplcode;

    This assumes that there is at most on "I" and one "O" row for unique values of the group by keys.