show data from multi column to single row

Question

in my oracle database contained the following data

http://i1207.photobucket.com/albums/bb476/daniwarrior/data-awal.jpg

I want to display the data as shown below

http://i1207.photobucket.com/albums/bb476/daniwarrior/data-aKHIR.jpg

Whitelist column are merging whitelist_pembayaran column, whitelist_pemasan, and whitelist_jenis_iklan

Blacklist column are merging blacklist_pembayaran column, blacklist_pemasang, and blacklist_jenis_iklan

whether the query in oracle can do? if you can how to query to display data like the picture above

*)sorry I can not show pictures because my reputation is less to be able to display the image, so I provide a link to the picture

Answer 1

This is string aggregation within group and string concatenation.

In Oracle 11g you can:

select 
   id_pegawai, 
   listagg(whitelist_pembayaran||whitelist_pemasan||whitelist_jenis_iklan,',') within group (order by id_pegawai)  as whitelist,
   listagg(blacklist_pembayaran||blacklist_pemasang||blacklist_jenis_iklan,',') within group (order by id_pegawai)  as blacklist
from table
group by id_pegawai;

(in Oracle <= 10g you may use wm_concat function)

But you have to deal with commas. Below a try for withelist(for blacklist is the same code):

select 
   id_pegawai, 
   listagg(whitelist_pembayaran||decode(whitelist_pembayaran,null,null,',')||
           whitelist_pemasan||decode(whitelist_pemasan,null, null, ',')||
           whitelist_jenis_iklan,',') 
   within group (order by id_pegawai)  as whitelist
from table
group by id_pegawai;

As explanation: the decode will put a comma after a field only if the field is not null. After whitelist_jenis_iklan is not needed a coma and listagg knows to deal with its comma.