list all the data, merge duplicate rows and sum the total using MySQL -

i have following sql

select   i.si_num `id`,   date_format(i.si_date, '%d-%b-%y') `date`,   i.si_tr `tr`,   d.dl_name `customer`,   i.si_net_value `net`,   datediff(curdate(), si_date) days,   t.value tval,   t.label label   invoices left join   dealer d on i.si_tr = d.dl_id left join   transactions t on i.si_num = t.invoice   i.si_tr = 'tr580494' order `si_num` desc; 

current output

+-----+-----------+----------+---------------------+---------+------+-------+-------+ | id  |   date    |    tr    |      customer       |   net   | days | tval  | label | +-----+-----------+----------+---------------------+---------+------+-------+-------+ | 404 | 18-feb-17 | tr580494 | starship enterprise | 109790  |   55 | 96070 | acr   | | 404 | 18-feb-17 | tr580494 | starship enterprise | 109790  |   55 | 10080 | crn   | | 404 | 18-feb-17 | tr580494 | starship enterprise | 109790  |   55 | 3640  | crn   | | 240 | 13-feb-17 | tr580494 | starship enterprise | 0       |   60 | null  | null  | | 239 | 13-feb-17 | tr580494 | starship enterprise | 81975   |   60 | 30405 | acr   | | 239 | 13-feb-17 | tr580494 | starship enterprise | 81975   |   60 | 51570 | crn   | | 132 | 3-feb-17  | tr580494 | starship enterprise | 38132.5 |   70 | 33282 | acr   | +-----+-----------+----------+---------------------+---------+------+-------+-------+ 

as can see there duplicate rows being generated id: 404 & 239 difference in these rows values in columns 'tval' & 'label'

the 'tval' , 'label' columns can populate more once per id, trying achieve every duplicate record want see if label 'crn' or 'arc' , sum values respective id , generate 1 row , transpose labels columns. please see below expected output.

expected output

+-----+-----------+----------+---------------------+---------+------+-------+-------+ | id  |   date    |    tr    |      customer       |   net   | days |  crn  |  acr  | +-----+-----------+----------+---------------------+---------+------+-------+-------+ | 404 | 18-feb-17 | tr580494 | starship enterprise | 109790  |   55 | 13720 | 96070 | | 240 | 13-feb-17 | tr580494 | starship enterprise | 0       |   60 | null  | null  | | 239 | 13-feb-17 | tr580494 | starship enterprise | 81975   |   60 | 51570 | 30405 | | 132 | 3-feb-17  | tr580494 | starship enterprise | 38132.5 |   70 | null  | 33282 | +-----+-----------+----------+---------------------+---------+------+-------+-------+ 

you can case inside sum:

select   i.si_num `id`,   date_format(i.si_date, '%d-%b-%y') `date`,   i.si_tr `tr`,   d.dl_name `customer`,   i.si_net_value `net`,   datediff(curdate(), si_date) days,   sum(case when t.label = 'acr' t.value else null end) acr   sum(case when t.label = 'crn' t.value else null end) crn   invoices left join   dealer d on i.si_tr = d.dl_id left join   transactions t on i.si_num = t.invoice   i.si_tr = 'tr580494' group  i.si_num,           date_format(i.si_date, '%d-%b-%y'),           i.si_tr,           d.dl_name,           i.si_net_value,           datediff(curdate(), si_date) order `si_num` desc;