Pivot Table dengan MySQL + PHP Bagian 1

Saya jarang bermain dengan pivot table yang umumnya indentik dengan laporan / reporting. Saya pernah membuat laporan pivot table dengan MySQL beberapa tahun lalu di suatu proyek pada badan pemerintahan dan semuanya bermain dengan PHP serta array.

Suatu waktu, saya bekerja onsite dan meminta Pivot Table dengan menggunakan SQL Server 2005 dan memang sungguh menyenangkan membentuk transformasi dari field ke kolom tanpa bantuan bahasa pemograman laras tinggi.

Suatu waktu, saya mendapatkan suatu pertanyaan mengenai pivot table dengan MySQL. Saya sempat garuk kepala untuk membuat query yang murni SQL dengan MySQL. Sayangnya waktu yang diberikan cukup menantang yaitu satu jam. Pivot Tablenya jadi (setengah doank *gubrak*) dan saya tidak begitu gembira dengan hasilnya karena 75% dilakukan di PHP. Jadi saya sempatkan waktu untuk menelaah apa yang bisa saya lakukan untuk  generasi data.

Mari kita bahas bareng – bareng deh. Kali pertama kita buat data mainan saja ya.

CREATE DATABASE IF NOT EXISTS dummy;

USE dummy;

CREATE TABLE aktifitas (

	id INT UNSIGNED AUTO_INCREMENT,

	nama VARCHAR(100) NOT NULL,

	proyek VARCHAR(10) NOT NULL,

	durasi INT UNSIGNED,

	PRIMARY KEY(id)

)ENGINE=MyISAM;

INSERT INTO aktifitas (nama, proyek, durasi) VALUES ('dityo', 'web', 100);

INSERT INTO aktifitas (nama, proyek, durasi) VALUES ('dityo', 'desktop', 50);

INSERT INTO aktifitas (nama, proyek, durasi) VALUES ('archy', 'web', 240);

INSERT INTO aktifitas (nama, proyek, durasi) VALUES ('satrio', 'web', 20);

INSERT INTO aktifitas (nama, proyek, durasi) VALUES ('arief', 'desktop', 90);

INSERT INTO aktifitas (nama, proyek, durasi) VALUES ('fiqri', 'desktop', 45);

INSERT INTO aktifitas (nama, proyek, durasi) VALUES ('arief', 'web', 30);

INSERT INTO aktifitas (nama, proyek, durasi) VALUES ('satrio', 'desktop', 120);

INSERT INTO aktifitas (nama, proyek, durasi) VALUES ('dityo', 'desktop', 45);

INSERT INTO aktifitas (nama, proyek, durasi) VALUES ('fiqri', 'desktop', 80);

INSERT INTO aktifitas (nama, proyek, durasi) VALUES ('archy', 'desktop', 45);

INSERT INTO aktifitas (nama, proyek, durasi) VALUES ('arief', 'web', 60);

Kita diminta untuk membuat report seperti ini

Coba kita melakukan perintah select

SELECT id, nama, proyek, durasi FROM aktifitas

Kita bisa melihat bahwa hasilnya adalah sebuah table log aktifitas dimana durasi adalah dalam menit. Kemudian, kita diminta untuk membuat suatu laporan mengenai keterlibatan orang – orang dalam proyek web dan desktop. Kita bisa melakukan grouping untuk berapa jam seseorang terlibat dalam proyek.

SELECT id, nama, proyek, durasi FROM aktifitas GROUP BY nama, proyek

Pada query di atas kita lihat bahwa saya ingin melihat data yang sudah digroup berdasarkan nama dan proyek. MySQL secara otomatis akan mereduksi kolom nama dan proyek yang isinya sama.

Namun, query ini ada yang kurang yah? coba periksa durasinya padahal nama saya tercantum dua kali untuk proyek desktop. Kok yang keluar cuma salah satu? nah, agar durasinya adalah total durasi dalam proyek, kita perlu menggunakan perintah agregasi yaitu SUM untuk menjumlahkan berapa lama saya terlibat dalam proyek desktop.

SELECT id, nama, proyek, SUM(durasi) FROM aktifitas GROUP BY nama, proyek;

Sekarang, jumlah durasi saya untuk proyek desktop sudah benar! Kita melakukan langkah berikutnya karena kita diminta untuk membuat laporan yang tidak membuat pusing kepala orang lain.

Dalam query kita bisa melakukan komputasi pada saat kita melakukan SELECT seperti SUM. Sekarang kita menggunakan query berserta komputasi untuk mengenerate informasi yang dihasilkan tampil horizontal.

SELECT 

	nama, 

	SUM(IF(proyek = 'desktop',durasi,0)) AS desktop, 

	SUM(IF(proyek = 'web', durasi, 0)) AS web 

FROM 

	aktifitas 

GROUP BY nama

Garuk kepala belum? apa sudah tepok jidat? Kode SQL di atas sebenarnya menyatukan nama kemudian kita membuat kolom baru dengan nama desktop dan web, pada saat query dijalankan maka akan dilakukan penjumlahan dengan syarat. Kalau field proyek itu adalah desktop maka tambahkan durasi pada kolom desktop, kalau bukan yah ditambahkan nol saja. Begitu juga dengan kolom web.

Dari laporan yang diminta masih ada lagi yang harus kita lakukan, karena manager meminta kita untuk menjumlahkan semua durasi dari setiap orang per proyek pada kolom bawah. Hayo, mulai garuk kepala lagi yaa … hahaha.

MySQL sudah menyiapkan suatu fungsi khusus yang bisa ditempel dengan GROUP BY yaitu WITH ROLLUP

SELECT 

	nama, 

	SUM(IF(proyek = 'desktop',durasi,0)) AS desktop, 

	SUM(IF(proyek = 'web', durasi, 0)) AS web 

FROM 

	aktifitas 

GROUP BY nama WITH ROLLUP;

Nah, cakep deh. Eits, ada lagi yang kurang tuh mas, si bos minta total durasi per masing – masing nama. ADUH! pak, ini udah jam 6.00 sore, mau pulang nih. Jiakakakakak. Manager pun berkata “Eits, ini kerjaan kudu jadi besok”. wekakakak, galau nggak sih loe?.

Teknik berikutnya adalah menggunakan sub query. Query yang tadi sudah kita buat tinggal di masukkan dalam query baru untuk menghasilkan jumlah durasi untuk masing – masing nama.

SELECT

	nama,

	desktop,

	web,

	desktop + web AS total_durasi

FROM (

	SELECT 

		nama, 

		SUM(IF(proyek = 'desktop',durasi,0)) AS desktop, 

		SUM(IF(proyek = 'web', durasi, 0)) AS web 

	FROM 

		aktifitas 

	GROUP BY nama WITH ROLLUP

) AS temp_table;

Jadi deh, bisa pulang ini. Kita lanjutin lagi besok yah untuk generate report menjadi tabel di PHP. Pak Bos, saya pulang dulu. Jiakakaka.

Silahkan lanjutkan ke Pivot Table dengan MySQL + PHP Bagian 2