Merge the same records that have the same id together in sql resultat:db2-CodePudding

Assuming I have a table containing the following information:

      ID        NAME     Boss           Main responsibility
      01        Tommy     x3             Yes
      02        Elis      x2             Yes
      02        Elis      x3             No
      03        John      x65            yes
      04        Lille     x50            yes

is there a way I can perform a select on the table to get the following(sql :DB2)

      ID        NAME        main responsibility
      01        Tommy                X3   
      02        Elis                 X2(main responsibility) AND X3   
      03        John                 X65
      04        Lille                x50

Thanks

CodePudding user response：

If your version of DB2 support it, you may aggregate and use the LISTAGG() function:

SELECT
    ID,
    NAME,
    LISTAGG(CONCAT(Boss, CASE WHEN main = 'Yes' THEN ' (main)' ELSE '' END), ', ')
        WITHIN GROUP(ORDER BY main DESC) AS main
FROM yourTable
GROUP BY ID, NAME
ORDER BY ID;