Md. Shamvil Hossain Md. Shamvil Hossain - 1 year ago 91
MySQL Question

Mysql query concate data into group concate

enter image description here

Here is my table. I have made the following query and get the result:

mysql - Query:


GROUP_CONCAT(CONCAT('', j0.rent_agree_id,'-',j0.rent_sche_id) ) AS agreement_ref_no
rent_provision_history AS j0

GROUP BY provision_date


enter image description here

But I want to see the result differently.

For example for the last result row i am getting now: 4-68,4-69,6-107,6-108,6-109

But I want to see it like that:

How can i do that?

Answer Source

You need two levels of aggregation:

SELECT provision_date,
       GROUP_CONCAT(rent_agree_id, '(', rsi, ')' SEPARATOR '|') agreement_ref_no
FROM (SELECT j0.rent_agree_id, GROUP_CONCAT(j0.rent_sche_id) as rsi
      FROM rent_provision_historyj0
      GROUP BY j0.provision_date, j0.rent_agree_id
     ) j0
GROUP BY j0.provision_date;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download