Group_Concat on PostgreSQL

Now i will explain how to select value from table such us group_concat from Mysql. We can use agregate function such as array_agg from PostgreSQL but, this function not too good for displaying the results as group_concat Mysql if we want to display data from 2 table with relations. Oke example i have table like this:

spp=> SELECT * FROM transaksi_spp;
 no_transaksi |  no_induk  |  tanggal   | dibayar |    bulan    
--------------+------------+------------+---------+-------------
            1 | 2010140419 | 2013-01-01 |   20000 | Januari
            2 | 2010140418 | 2013-01-05 |   22000 | Januari
            3 | 2010140419 | 2013-02-01 |   20000 | Februari
            4 | 2010140418 | 2013-03-01 |   22000 | Februari
           11 | 2010140419 | 2013-03-01 |   20000 | Maret
           12 | 2010140418 | 2013-03-02 |   44000 | April,Maret
           13 | 2010140420 |            |       0 | 
           15 | 2010140421 |            |       0 | 
(8 rows)

spp=> 


And to display as group_concat, we will change command select like this:

spp=> SELECT no_induk, array_agg(bulan) AS bulan FROM transaksi_spp GROUP BY no_induk;
  no_induk  |              bulan               
------------+----------------------------------
 2010140421 | {NULL}
 2010140419 | {Januari,Februari,Maret}
 2010140420 | {NULL}
 2010140418 | {Januari,Februari,"April,Maret"}
(4 rows)

spp=>

Or if you want to display in the string mode and not in the array mode, use this command:

spp=> SELECT no_induk, array_to_string(array_agg(bulan), ', ') AS bulan FROM transaksi_spp GROUP BY no_induk;
  no_induk  |             bulan              
------------+--------------------------------
 2010140421 | 
 2010140419 | Januari, Februari, Maret
 2010140420 | 
 2010140418 | Januari, Februari, April,Maret
(4 rows)

spp=> 

And as described above, if I want to display the two tables are related, The results were not too good.

spp=> SELECT B.no_induk, B.nama_siswa, array_agg(C.bulan) AS bulan FROM siswa B, transaksi_spp C WHERE B.no_induk = C.no_induk GROUP BY B.no_induk ORDER BY B.no_induk;
  no_induk  |   nama_siswa    |              bulan               
------------+-----------------+----------------------------------
 2010140418 | Arif Hermansyah | {"April,Maret",Februari,Januari}
 2010140419 | Imron Rosdiana  | {Maret,Februari,Januari}
 2010140420 | Sarah Idayasa   | {NULL}
 2010140421 | Asih Restari    | {NULL}
(4 rows)

spp=>

See column bulan in the reverse order 😦

Advertisements

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s