So I have different columns cn_ref_no,date,expiry,amount,cn_commission,cn_amount_tag,bill_ref

So I have table with columns

The code I am using is

select distinct(a.cn_ref_no),a.cn_commission,a.cn_tag,b.b_ref,b.b_commission,b.b_tag,c.d_ref,c.d_tag,c.d_commission
from cn_table a
left join bill_table b
on a.cn_ref=b.our_lc_ref

left join disc_table c
on a.cn_ref=c.our_lc_ref

The table

cn_ref_no	cn_commission	cn_tag	b_ref	b_commission	b_tag	d_ref	d_tag	d_commission
CN001	100	CN_CNF	B001	20	BILL_EXC	D001	DISC_SWF	20
CN001	100	CN_CNF	B001	20	BILL_EXC	D001	DISC_ADV	30
CN001	100	CN_CNF	B001	20	BILL_EXC	D001	DISC_CNF	50
CN001	100	CN_CNF	B001	35	BILL_ADV	D001	DISC_SWF	20
CN001	100	CN_CNF	B001	35	BILL_ADV	D001	DISC_ADV	30
CN001	100	CN_CNF	B001	35	BILL_ADV	D001	DISC_CNF	50
CN001	100	CN_CNF	B001	40	BILL_SWF	D001	DISC_SWF	20
CN001	100	CN_CNF	B001	40	BILL_SWF	D001	DISC_ADV	30
CN001	100	CN_CNF	B001	40	BILL_SWF	D001	DISC_CNF	50

There are many differents records like this, this is one example.

What I want is data in this format

cn_ref_no	cn_commission	cn_tag	b_ref	b_commission	b_tag	d_ref	d_tag	d_commission
CN001	100	CN_CNF	B001	20	BILL_EXC	D001	DISC_SWF	20
CN001	100	CN_CNF	B001	35	BILL_ADV	D001	DISC_ADV	30
CN001	100	CN_CNF	B001	40	BILL_SWF	D001	DISC_CNF	50

Or the records in a single row like this if possible

cn_ref_no	cn_commission	cn_tag	b_ref	b_commission	b_tag	d_ref	d_tag	d_commission
CN001	100	CN_CNF	B001	20 35 40	BILL_EXC BILL_ADV BILL_SWF	D001	DISC_SWF DISC_ADV DISC_CNF	20 30 50

CodePudding user response：

You could use group_concat and group by

Select a.cn_ref_no,a.cn_commission,a.cn_tag,b.b_ref,b.b_commission
  , group_concat(b.b_tag)
    ,c.d_ref
   ,group_concat(c.d_tag)
    ,c.d_commission
from cn_table a
left join bill_table b on a.cn_ref=b.our_lc_ref
left join disc_table c on a.cn_ref=c.our_lc_ref
group by a.cn_ref_no,a.cn_commission
    ,a.cn_tag,b.b_ref,b.b_commission, c.d_ref,c.d_commission

CodePudding user response：

You can GROUP BY the unique columns and the rest You can GROUP_CONCAT, which can use DISTINCT and ORDER BY

select a.cn_ref_no,a.cn_commission,a.cn_tag,b.b_ref,b.b_commission
   ,GROUP_CONCAT(DISTINCT b.b_tag),c.d_ref
   ,GROUP_CONCAT(DISTINCT c.d_tag)
   ,GROUP_CONCAT(DISTINCT  c.d_commission ORDER BY c.d_commission ASC)
from cn_table a
left join bill_table b
on a.cn_ref=b.our_lc_ref

left join disc_table c
on a.cn_ref=c.our_lc_ref
GROUP BY a.cn_ref_no,a.cn_commission,a.cn_tag,b.b_ref,b.b_commission,c.d_ref