1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Group_concat into group_concat with two different separators

Discussion in 'MySQL' started by kost36, Nov 17, 2020.

  1. kost36

    kost36 New Member

    Joined:
    Nov 14, 2020
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    I use the follown as one movie's page
    Code:
         SELECT
        `mt_films`.`Title1` AS `Title1`,
        GROUP_CONCAT(DISTINCT `t_idiotita`.`idiotita`,
            ': ',
            `t_people`.`person`
            ORDER BY `t_idiotita`.`cnt_gr_id` ASC
            SEPARATOR ' ') AS `kati`
    FROM
        ((`t_people`
        JOIN (`st_peoplefilms`
        JOIN `mt_films` ON ((`mt_films`.`ID_films` = `st_peoplefilms`.`ID_films`))) ON ((`t_people`.`ID_person` = `st_peoplefilms`.`ID_person`)))
        JOIN `t_idiotita` ON ((`st_peoplefilms`.`ID_idiotita` = `t_idiotita`.`ID_idiotita`)))
    WHERE
        ((`st_peoplefilms`.`ID_idiotita` = 29)
            OR (`st_peoplefilms`.`ID_idiotita` = 33)
            OR (`st_peoplefilms`.`ID_idiotita` = 27)
            OR (`st_peoplefilms`.`ID_idiotita` = 12)
            OR (`st_peoplefilms`.`ID_idiotita` = 43)
            OR (`st_peoplefilms`.`ID_idiotita` = 11)
            OR (`st_peoplefilms`.`ID_idiotita` = 32)
            OR (`st_peoplefilms`.`ID_idiotita` = 5)
            OR (`st_peoplefilms`.`ID_idiotita` = 24)
            OR (`st_peoplefilms`.`ID_idiotita` = 14))
    GROUP BY `mt_films`.`Title1`
    
    That returns e.g.

    Title1 | Dir.: John | Script: Peter | Script: Anna | ...
    Title2 | Dir.: George | Dir.: Paul | Script: Gus | ...

    But while for many many movies work more than one person for the same specialty i need a second group_concat to group all the persons for the same specilty. I also need two different separations. The first ": " separates the specialties and the second ", " the persons

    Expected result

    Title1 | Dir.: John | Script: Peter, Anna | ...
    Title2 | Dir.: George, Paul | Script: Gus | ...

    Could you pls help?
    Thank's
     
  2. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    39
    Likes Received:
    17
    Trophy Points:
    8
    Gender:
    Male
    Give you an example here:
    Code:
    select  a,group_concat(concat(b,' ',c)) from
    (select emp_dept as a,emp_grade as b,group_concat(distinct emp_sex SEPARATOR ' ') as c from emp_small where emp_dept<'B' group by emp_dept,emp_grade) dt
    group by a ;
    
    subquery result:
    'AAA', '1000', 'F M'
    'AAA', '1001', 'M'
    'AAA', '1003', 'M'
    'AAA', '1005', 'M'
    'AAA', '1006', 'F'
    'AAA', '1012', 'M'
    'AAA', '1014', 'F M'
    'AAA', '1020', 'F'
    'AAA', '1025', 'F'
    'AAA', '1027', 'F'
    'AAA', '1029', 'M'

    Final result:
    'AAA', '1000 F M,1001 M,1003 M,1005 M,1006 F,1012 M,1014 F M,1020 F,1025 F,1027 F,1029 M
    'ABC', '1001 F,1002 F,1011 M,1012 F,1013 M,1027 M,1029 M,1033 M,1040 F,1049 M,1050 F,1052 M
    ....
     
    Last edited by a moderator: Nov 19, 2020
  3. kost36

    kost36 New Member

    Joined:
    Nov 14, 2020
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Hey Richard,
    didn't manage to make it works
     
  4. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    39
    Likes Received:
    17
    Trophy Points:
    8
    Gender:
    Male
    I try my best to understand your SQL, you may try this, the key factor is that whether the grouping of `t_idiotita`.`cnt_gr_id` is applicable, it is up to your judgement.

    SELECT
    dt.`Title1` AS `Title1`,
    GROUP_CONCAT(DISTINCT dt.`idiotita`,
    ': ',
    dt.`person`
    ORDER BY dt.`cnt_gr_id` ASC
    SEPARATOR ' ') AS `kati` from
    (SELECT
    `mt_films`.`Title1` AS `Title1`,
    `t_idiotita`.`idiotita` as idiotita,
    `t_idiotita`.`cnt_gr_id` as `cnt_gr_id`
    GROUP_CONCAT(DISTINCT`t_people`.`person`
    SEPARATOR ',') AS `persons`
    FROM ...
    .......
    group by `mt_films`.`Title1`,`t_idiotita`.`idiotita`,`t_idiotita`.`cnt_gr_id`) dt
    GROUP BY dt.`Title1`
     

Share This Page