DB2 SQL : How to get 2 rows data into 1

Question

I am getting employee data from source which gives me 2 rows for same employee. 1st row has a salary and 2nd has commission. to identify if it is salary or commission I have got one Flag column.

Now I want to store it in single row in my target table, where I would have salary and commision as columns.

Sample data and desired results would help. Do all employees have exactly two rows? Are there other columns? — Gordon Linoff, Oct 28 '20 at 12:20

score 0 · Answer 1 · answered Oct 28 '20 at 05:12

0

Use conditional aggregation

select employee_id,max(case when flag=0 then salary end) as salary,
       max(case when flag=1 then commission end) as commission
from tablename
group by employee_id

answered Oct 28 '20 at 05:12

Fahmi

37,315
5
22
31

score 0 · Accepted Answer · answered Oct 28 '20 at 06:01

try a chance maybe work

select employee_id,sum(salary)salary,sum(commission) from
 (select employee_id,0 as salary,commission from tblname where flag=1
    union all
  select  employee_id ,salary ,0 commission from tblname where flag=0
  )a
group by employee_id

DB2 SQL : How to get 2 rows data into 1

2 Answers2