MySQL Forums
Forum List  »  General

Re: Mysql Query to concat values based on Unique key
Posted by: Peter Brawley
Date: April 29, 2022 02:09PM

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.

Options: ReplyQuote


Subject
Written By
Posted
Re: Mysql Query to concat values based on Unique key
April 29, 2022 02:09PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.