Re: Mysql Query to concat values based on Unique key

[Peter Brawley]

The Group_Concat() calls in that query mangle commas & parens. I can't test it directly since its table names don't match any DDL you've posted, but using the `input` table DDL you posted, the first Group_Concat() using the logic you've attempted would be more like ...

select pickno, 
       GROUP_CONCAT( CASE 
                       WHEN `code`='R' THEN 
                         CONCAT( "name-" ,`username`, ",reason-" ,
                                 RTRIM(`reasonorshipinstrction`) 
                               ) 
                     END, 
                     ", " 
                   )
from input
group by pickno;

... but it produces pretty unreadable results ...

| L11230 | name-Tuli,reason-side cracks, ,name-Tuli,reason-Funnel issue, ,name-Tuli,reason-side cracks, ,name-Tuli,reason-Funnel issue,
                                                                                                                       |
| M12401 | name-Mina,reason-Random repair, ,name-Alex,reason-allergen, ,name-Mina,reason-Random repair, ,name-Alex,reason-allergen,

... so it appears you need to rethink that reporting logic.

And I should add ...

  select *, group_concat(...)
  group by pickno

will raise an error unless sql_mode does not include only_full_group_by, see https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html



Edited 2 time(s). Last edit at 05/04/2022 12:59PM by Peter Brawley.