Merge two rows in sql join statement

Question

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:

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

How can I achieve this?

Did you try `pivot` ? [check this](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — dwij, Oct 02 '17 at 04:38
Nice description here aswell. However, I find easier to implement GROUP_CONCAT() func but thanks for the suggestion! — , Oct 02 '17 at 04:50

score 2 · Accepted Answer · answered Oct 02 '17 at 04:39

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;

score 0 · Answer 2 · edited Oct 02 '17 at 04:52

0

Found an answer here:

SELECT A.id AS plan_id,
       A.start_date,
       A.end_date,
       group_concat(C.title, '-') AS 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'
GROUP BY plan_id

edited Oct 02 '17 at 04:52

zarruq

2,445
2
10
19

answered Oct 02 '17 at 04:40

Merge two rows in sql join statement

2 Answers2