Pivot Tabel Postgres

Lagi seneng mainan gajah (postgres), dan kali ini pengen merekam ilmu yang kuperoleh saat mengolah report pengiriman barang dalam jangka waktu tertentu. Mmm... biasanya laporan bulanan berupa grafik, atau tabel yang mudah dibaca, ringkas dan jelas. 

Sebagai contoh ada beberapa item yang dijual  dan dalam waktu 3 bulan (Jan-Peb-Mar), tiap item tesebut akan terjual x-buah. Data transaksi harian akan tersimpan secara vertikal di dalam database. Untuk menampilkan data secara pivot (bulan ditampilkan horisontal/sumbu X dan itemnya vertikal/sumbu Y), aku menggunakan query yang panjaaang, atau dengan bantuan script menampilkan data sesuai dengan permintaan(ringkas), walupun data yang diquery tidk pivot. Cara-caya yang aku gunkan ini ribet sekali.

Tak kusadari ternyata di postgres sudah ada function untuk pivot data. yah.. lebih baik terlambat daripada tidak tahu sama sekali. Pivot data di postgres menggunakan fucntion crosstab, function ini ada dalam tablefunc.sql. Jika tablefunc.sql belum terinstall dan langsung memakai crosstab maka akan muncul error seperti ini "function crosstab(unknown, unknown) does not exist ". Untuk mengatasinya simak langkah-langkah yang aku praktekan berikut ini:

1. Install tablefunc.sql (aku pakai ubuntu dan postgres 8.4),

tulis perintah ini:

psql -h loaclhost -U postgres -d dbname -f /usr/share/postgresql/8.4/contrib/tablefunc.sql

Hasilnya akan terlihat seperti ini:

 2. Function sudah terinstall dan sudah siap di gunakan, 

contoh penggunaan crosstab bisa dilihat melalui contoh berikut ini:

create table sales(id_item int, tanggal date, item text, qty int);
insert into sales values(1,'2011-12-28', 'laptop', 1000);
insert into sales values(2,'2012-01-23', 'handphone', 1500);
insert into sales values(1,'2012-01-19', 'laptop', 500);
insert into sales values(3,'2012-02-11', 'projector', 500);
insert into sales values(4,'2012-02-28', 'pc', 1500);
insert into sales values(1,'2012-02-29', 'laptop', 100);
insert into sales values(5,'2012-03-08', 'tablet', 2000);
insert into sales values(1,'2012-03-10', 'laptop', 50);
insert into sales values(6,'2012-03-11', 'ups', 1000);

select * from crosstab(
  'select item, to_char(tanggal, ''mon'')::text As bln 
, sum(qty) as total_qty from sales
  WHERE tanggal BETWEEN ''2011-12-01'' and ''2013-01-31''
  GROUP BY id_item, item, to_char(tanggal, ''mon'')
  order by id_item asc',
  'SELECT to_char(date ''2011-12-01'' + (n || ''month'')::interval,
 ''mon'') As short_mname 
  FROM generate_series(0,11) n'
) as (
  item text,
  "Dec" int,
  "Jan" int,
  "Feb" int,
  "Mar" int,
  "Apr" int,
  "May" int,
  "Jun" int,
  "Jul" int,
  "Aug" int,
  "Sep" int,
  "Oct" int,
  "Nov" int  
);

Hasil dari query diatas: 

Semakin banyak belajar, semakin banyak hal baru yang didapat, 

dan rasanya apa yang aku ketahui masih sangat terbatas... ^^'