how to merge job title with same person in postgresql

Question

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                  |
+-------+------+--------+--------+-----------------------+

Answer 1

your data

Create temporary table tmp_anggota (
  id INTEGER NOT NULL, 
  nama VARCHAR(50) NOT NULL, 
  nik VARCHAR(50) NOT NULL, 
  no_kta VARCHAR(50) NOT NULL, 
  alamat VARCHAR(10) NOT NULL
);
INSERT INTO tmp_anggota (id, nama, nik, no_kta, alamat) 
VALUES 
  (4, 'abas', '0001', '0021', 'a'), 
  (5, 'panji', '0002', '0022', 'b'), 
  (6, 'doni', '0003', '0023', 'c');
create temporary table tmp_pengurus (
  id INTEGER NOT NULL, 
  id_anggota INTEGER NOT NULL, 
  jabatan VARCHAR(10) NOT NULL
);
INSERT INTO tmp_pengurus(id, id_anggota, jabatan) 
VALUES 
  (1, 4, 'ketua'), 
  (2, 6, 'bendahara'), 
  (3, 6, 'sekretaris');

use string_agg as follows

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

or by using subquery

SELECT nama,
       nik,
       no_kta,
       alamat,
       jabatan
FROM   (SELECT id_anggota,
               String_agg(jabatan, ',') AS jabatan
        FROM   tmp_pengurus
        GROUP  BY id_anggota) P
       FULL JOIN tmp_anggota a
              ON a.id = p.id_anggota
ORDER  BY nama ASC,
          alamat ASC