how to merge job title with same person in postgresql-CodePudding

I have 2 tables, table anggota and pengurus where the tables are merged, where for example there is the same name with different jabatan then jabatan is combined into one row

tbl anggota :

 ---- ------- ------ -------- -------- 
| id | nama  | nik  | no_kta | alamat |
 ---- ------- ------ -------- -------- 
| 4  | abas  | 0001 | 0021   |    a   |
| 5  | panji | 0002 | 0022   |    b   |
| 6  | doni  | 0003 | 0023   |    c   |
 ---- ------- ------ -------- --------

tbl pengurus :

 ---- ------------- ----------- 
| id | id_anggota  | jabatan   |
 ---- ------------- ----------- 
| 1  |     4       | ketua     |
| 2  |     6       | bendahara |
| 3  |     6       | sekretaris|
 ---- ------------- -----------

postgresql : select a.nama, a.nik, a.no_kta, a.alamat, p.jabatan from anggota a left join pengurus p on a.id = p.id_anggota where a.id_parpol = 4

 ------- ------ -------- -------- ------------ 
| nama  | nik  | no_kta | alamat | jabatan    |
 ------- ------ -------- -------- ------------ 
| abas  | 0001 | 0021   |    a   | ketua      |
| doni  | 0003 | 0023   |    c   | bendahara  |
| doni  | 0003 | 0023   |    c   | sekretaris |
| panji | 0002 | 0022   |    b   | null       |
 ------- ------ -------- -------- ------------

I want the data to come out like this :

 ------- ------ -------- -------- ----------------------- 
| nama  | nik  | no_kta | alamat | jabatan               |
 ------- ------ -------- -------- ----------------------- 
| abas  | 0001 | 0021   |    a   | ketua                 |
| doni  | 0003 | 0023   |    c   | bendahara, sekretaris |
| panji | 0002 | 0022   |    b   | null                  |
 ------- ------ -------- -------- -----------------------

CodePudding user response：

use string_agg as follows

SELECT nama,
       nik,
       no_kta,
       alamat,
       String_agg(jabatan, ',') AS jabatan
FROM   anggota a
       FULL JOIN pengurus p
         ON a.id = p.id_anggota
GROUP  BY nama,
          nik,
          no_kta,
          alamat

or by using subquery

 SELECT nama,
       nik,
       no_kta,
       alamat,
       jabatan
FROM   (
                SELECT   id_anggota ,
                         string_agg(jabatan,',') AS jabatan
                FROM     pengurus
                GROUP BY id_anggota ) P
FULL JOIN   anggota a
ON     a.id=p.id_anggota