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 A.id as plan_id, A.start_date, A.end_date, C.title
from cp_plan A left outer join cp_plan_zone_map B
on A.id = B.plan_id left outer join cp_zone C
on B.zone_id = C.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
    A.id AS plan_id,
    A.start_date,
    A.end_date,
    GROUP_CONCAT(C.title, '-') AS title
FROM cp_plan A
LEFT JOIN cp_plan_zone_map B 
    ON A.id = B.plan_id
LEFT JOIN cp_zone C 
    ON B.zone_id = C.id
WHERE
    A.status = 'in-planning'
GROUP BY
    A.id,
    A.start_date,
    A.end_date;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download