How to concatenate names in mysql

Question

I have query:

SELECT YEAR(t1.changedtime) as year, MONTHNAME(t1.changedtime) as months, t6.pname FROM `working_scene` t1 
LEFT JOIN( SELECT longname, username, user_type, email, uid, staff_id, access, fkdeptid, fkhouseid, fkhouseroleid FROM `users` )t2 ON t2.uid = t1.owner 
LEFT JOIN( SELECT longname as squad_leader, uid FROM `users` )t3 ON t3.uid = t1.fkslid 
LEFT JOIN( SELECT weid, fkwpid FROM `working_episode` )t4 ON t4.weid = t1.fkweid 
LEFT JOIN( SELECT wpid, fkpid, fkrftid FROM `working_project` )t5 ON t5.wpid = t4.fkwpid 
LEFT JOIN( SELECT pid, pname FROM `master_project` )t6 ON t6.pid = t5.fkpid WHERE t2.staff_id = '1000368' GROUP BY t6.pname, months ORDER BY changedtime ASC

Which will give me the result:

How can I delete month duplication and print project names coma separated within one month?

For example:

| year |  months  |  pname   |
+------+----------+----------+
| 2016 | November | Hanazuki |
| 2016 | November | Niko     |

To be:

| year |  months  |     pname      |
+------+----------+----------------+
| 2016 | November | Hanazuki, Niko |

EDIT

I tried group_concat but it didnt work the way I wanted. Here is what

SELECT YEAR(t1.changedtime) as year, 
       MONTHNAME(t1.changedtime) as months,
       GROUP_CONCAT(t6.pname)
FROM `working_scene` t1 
      LEFT JOIN( SELECT longname, username, user_type, email, uid, staff_id, access, fkdeptid, fkhouseid, fkhouseroleid FROM `users` )t2 ON t2.uid = t1.owner 
      LEFT JOIN( SELECT longname as squad_leader, uid FROM `users` )t3 ON t3.uid = t1.fkslid 
      LEFT JOIN( SELECT weid, fkwpid FROM `working_episode` )t4 ON t4.weid = t1.fkweid 
      LEFT JOIN( SELECT wpid, fkpid, fkrftid FROM `working_project` )t5 ON t5.wpid = t4.fkwpid 
      LEFT JOIN( SELECT pid, pname FROM `master_project` )t6 ON t6.pid = t5.fkpid 
WHERE t2.staff_id = '1000368' 
GROUP BY YEAR(t1.changedtime), MONTHNAME(t1.changedtime)
ORDER BY changedtime ASC

How to use `group_concat`: https://stackoverflow.com/questions/13451605/how-to-use-group-concat-in-a-concat-in-mysql — Yusuf Hassan, Feb 01 '18 at 08:11

score 1 · Accepted Answer · answered Feb 01 '18 at 08:09

MySQL has built-in aggregate function for that called GROUP_CONCAT.

SELECT YEAR(t1.changedtime) as year, 
       MONTHNAME(t1.changedtime) as months,
       GROUP_CONCAT(t6.pname)
FROM `working_scene` t1 
      LEFT JOIN( SELECT longname, username, user_type, email, uid, staff_id, access, fkdeptid, fkhouseid, fkhouseroleid FROM `users` )t2 ON t2.uid = t1.owner 
      LEFT JOIN( SELECT longname as squad_leader, uid FROM `users` )t3 ON t3.uid = t1.fkslid 
      LEFT JOIN( SELECT weid, fkwpid FROM `working_episode` )t4 ON t4.weid = t1.fkweid 
      LEFT JOIN( SELECT wpid, fkpid, fkrftid FROM `working_project` )t5 ON t5.wpid = t4.fkwpid 
      LEFT JOIN( SELECT pid, pname FROM `master_project` )t6 ON t6.pid = t5.fkpid 
WHERE t2.staff_id = '1000368' 
GROUP BY YEAR(t1.changedtime), MONTHNAME(t1.changedtime)
ORDER BY changedtime ASC

I tried it but it stores all the names inside one row. Please see edit — Yevgeniy Bagackiy, Feb 01 '18 at 08:29
@YevgeniyBagackiy add `DISTINCT` keyword inside the function, `GROUP_CONCAT(DISTINCT t6.pname)` — John Woo, Feb 01 '18 at 08:32

How to concatenate names in mysql

1 Answers1