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:
    59
    Likes Received:
    19
    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:
    59
    Likes Received:
    19
    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

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice