Safan Momin Safan Momin - 3 years ago 163
SQL Question

Merge two rows in sql join statement

My join query looks like this:

select distinct as plan_id, A.start_date, A.end_date, C.title
from cp_plan A left outer join cp_plan_zone_map B
on = B.plan_id left outer join cp_zone C
on B.zone_id = where A.status = 'in-planning'

This gives me the following result:

enter image description here

Since my plan id, start date and end date are all same, all I want is to merge the title like this:

enter image description here

How can I achieve this?

Answer Source

Aggregate over the plan_id, start_date, and end_date, and use GROUP_CONCAT to generate the hyphen separated list of titles:

SELECT AS plan_id,
    GROUP_CONCAT(C.title, '-') AS title
FROM cp_plan A
LEFT JOIN cp_plan_zone_map B 
    ON = B.plan_id
LEFT JOIN cp_zone C 
    ON B.zone_id =
    A.status = 'in-planning'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download