Saturday 25 February 2017

GROUP_CONCAT di PostgreSQL

Terkadang, ketika melakukan query GROUP BY, kita ingin melihat nilai-nilai pada kolom tertentu, jika di MySQL ada GROUP BY [1], bagaimana kita melakukannya di PostgreSQL ?

Alhamdulillah, di PostgreSQL kita dapat melakukannya dengan string_agg [2][3][4]

SELECT a.farm_id, string_agg(DISTINCT a.name, ',') as animals, string_agg(DISTINCT t.name, ',') as tools FROM animal a, tool t WHERE a.farm_id = t.farm_id GROUP BY a.farm_id;

Referensi

  1. Merangkai (Concatenation) String pada Query Agregasi di MySQL, http://workshop.openthinklabs.com/resources/sistem-basis-data-database/mysql-bootcamp/buku-masak-a-k-a-cookbook/merangkai-concatenation-string-pada-query-agregasi-di-mysql
  2. string_agg, https://www.postgresql.org/docs/9.5/static/functions-aggregate.html
  3. Postgres GROUP_CONCAT, https://coderwall.com/p/eyknwa/postgres-group_concat
  4. Aggregate Expressions, https://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES