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

Question

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

Answer 1

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;