Pivot dan Cross-tab Query

Pivot atau cross-tab query sering digunakan untuk mendapatkan informasi dari dimensi yang berbeda. Sesuai rencana Dzaky, setelah minggu lalu membahas join dan group, dilanjutkan pada posting kali ini tentang pivot atau cross-tab query. Sebelum kepermasalahan kita lihat kembali salah satu contoh pada posting terdahulu. Untuk mendapatkan kembali informasi lengkap dari kedua tabel, dilakukan dengan klausa JOIN.


SELECT t.tanggal, u.uraian, u.kode, t.jumlah
FROM transaksi t JOIN ( turai u )
WHERE t.pk = u.pk;

dan hasilnya bisa dilihat disini

tanggal	uraian	kode	Jumlah
2012-02-01	Makan siang	K	15000
2012-02-02	Makan siang	K	12500
2012-02-01	Makan malam	K	12500
2012-02-02	Makan malam	K	20000
2012-04-19	Makan malam	K	2460
2012-02-01	Beli rokok	K	10000
2012-02-02	Beli rokok	K	10000
2012-04-03	Beli rokok	K	25000
2012-04-03	Beli rokok	K	25000
2012-04-03	Beli rokok	K	25000
2012-04-03	Beli rokok	K	25000
2012-02-01	Makan Sate	K	25000
2012-04-03	Makan Sate	K	40000
2012-04-03	Makan Sate	K	40000
2012-04-03	Makan Sate	K	40000
2012-04-03	Makan Sate	K	40000
2012-02-02	Sarapan Pagi	K	5000
2012-01-31	Honor	M	750000
2012-02-05	Honor	M	500000

Dari informasi tersebut ada titik temu antara tanggal dan uraian. Misal tanggal berapa saja membeli rokok, sekaligus mengetahui berapa uang yang dikeluarkan pada tanggal ataupun untuk uraian tersebut. Untuk kasus pertama Dzaky akan tampilkan informasi yang dimaksud.

SELECT IFNULL(t.tanggal,"TOTAL") AS tanggal,
SUM(CASE t.pk WHEN 1 THEN -t.jumlah ELSE 0 END) makan_siang,
SUM(CASE t.pk WHEN 2 THEN -t.jumlah ELSE 0 END) makan_malam,
SUM(CASE t.pk WHEN 3 THEN -t.jumlah ELSE 0 END) beli_rokok,
SUM(CASE t.pk WHEN 4 THEN -t.jumlah ELSE 0 END) makan_sate,
SUM(CASE t.pk WHEN 5 THEN -t.jumlah ELSE 0 END) sarapan_pagi,
SUM(CASE t.pk WHEN 6 THEN t.jumlah else 0 END) honor
FROM transaksi t JOIN ( turai u )
WHERE t.pk = u.pk
GROUP BY t.tanggal WITH ROLLUP

dan hasilnya bisa dilihat buka/tutup

tanggal	makan siang	makan malam	beli rokok	makan sate	sarapan	honor
2012-01-31	0	0	0	0	0	750000
2012-02-01	-15000	-12500	-10000	-25000	0	0
2012-02-02	-12500	-20000	-10000	0	-5000	0
2012-02-05	0	0	0	0	0	500000
2012-04-03	0	0	-100000	-160000	0	0
2012-04-19	0	-2460	0	0	0	0
TOTAL	-27500	-34960	-120000	-185000	-5000	1250000

Informasi yang didapatkan adalah tanggal terhadap setiap uraian. Lihat posting Dzaky tentang Ifnull: Roll Up dan join & group jika anda masih belum memahami pernyataan query diatas.

Informasi akan semakin menarik, bukan saja menjumlahkan perkolom (jumlah untuk setiap kolom) tetapi juga jumlah untuk setiap barisnya (jumlah berdasarkan tanggal) dan pada kolom terakhir yaitu kolom paling kanan bawah haruslah total keseluruhan nilai (setelah ditambah atau dikurangi).

Ada dua tahapan, pertama mendapatkan hasil seperti query diatas dan kedua menghitung setiap barisnya. Kedua langgakh ini akan Dzaky lakukan dengan menggunakan hasil pertama sebagai sub query.

SELECT g.tanggal, g.makan_siang, g.makan_malam,
g.beli_rokok, g.makan_sate, g.sarapan_pagi,
g.honor, (g.makan_siang + g.makan_malam +
g.beli_rokok + g.makan_sate + g.sarapan_pagi +
g.honor) As total
FROM (SELECT ifnull(t.tanggal,"TOTAL") AS Tanggal,
SUM(CASE t.pk WHEN 1 THEN -t.jumlah ELSE 0 END) makan_siang,
SUM(CASE t.pk WHEN 2 THEN -t.jumlah ELSE 0 END) makan_malam,
SUM(CASE t.pk WHEN 3 THEN -t.jumlah ELSE 0 END) beli_rokok,
SUM(CASE t.pk WHEN 4 THEN -t.jumlah ELSE 0 END) makan_sate,
SUM(CASE t.pk WHEN 5 THEN -t.jumlah ELSE 0 END) sarapan_pagi,
SUM(CASE t.pk WHEN 6 THEN t.jumlah ELSE 0 END) honor
FROM transaksi t JOIN ( turai u )
WHERE t.pk = u.pk
GROUP BY t.tanggal WITH ROLLUP) AS g

dan hasilnya bisa dilihat buka/tutup

tanggal	makan siang	makan malam	beli rokok	makan sate	sarapan	honor	total
2012-01-31	0	0	0	0	0	750000	750000
2012-02-01	-15000	-12500	-10000	-25000	0	0	-62500
2012-02-02	-12500	-20000	-10000	0	-5000	0	-47500
2012-02-05	0	0	0	0	0	500000	500000
2012-04-03	0	0	-100000	-160000	0	0	-260000
2012-04-19	0	-2460	0	0	0	0	-2460
TOTAL	-27500	-34960	-120000	-185000	-5000	1250000	877540

Ah..ha.. informasinya lengkap sudah. Mudah-mudahan psoting Dzaky dapat bermanfaat buat semua. Jika pembaca suka, pembaca boleh -like this- yo..

When someone shares something of value with you and you benefit from it, you have a moral obligation to share it with others.

z35W7z4v9z8w