Nurasto.com Website

My digital scrapbook

Pivot Table dengan MySQL + PHP Bagian 1

on under Software and Web Development11 Comments

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

Nama Desktop Web Total Durasi
Total Durasi 0 0 0
Archy 0 0 0
Arief 0 0 0
Dityo 0 0 0
Fiqri 0 0 0
Satrio 0 0 0

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

  • Beuhhhhhh mantep banget!! makasih ommm…sippp

  • This is very intriguing, You are a quite skilled blogger. I have joined your rss feed and look forward to looking for far more of your wonderful post. Also, I’ve shared your internet web site in my social networks!

  • Shadiq

    Mantap banget gan, tapi ane mau nanya nih, saya punya kasus mirip ini.asusi semua kolom tabel sama, kalo misalnya proyek dilakukan dalam 1 tahun maksimal 3 kali, kolom proyek isinya tanggal pelaksanaan proyek (bukan ‘web’ dan ‘browser’) yang tidak ada frekuensi tertentu alias tiap orang bisa melakukan kapan saja dalam tahun tersebut, bagaimana cara membuat tabel laporannya dengan header ‘nama’, ‘proyek1’, ‘proyek2’, ‘proyek3’, ‘total’? ane udah garuk2 kepala n tepok jidat nih masih belum ketemu juga. terima kasih banyak sebelumnya.

  • Tomas

    di contoh hanya ada dua proyek (desktop dan web), bagaimana jika ada lebih dan kita tidak bisa tentukan jumlahnya?

    • Saya menggunakan text disana, jadi bisa memanfaatkan distinct untuk mendapatkan proyek – proyek yang tersimpan terlebih dahulu dan melakukan generasi kolom melalui bantuan bahasa pemograman seperti PHP.

      Jika menggunakan table lain untuk menyimpan data proyek, bisa kita gunakan bantuan bahasa pemograman untuk mengenerate kolom – kolom untuk di sum berdasarkan record dari table proyek yang kemudian di simpan sebagai variable, setelah itu kita eksekusi querynya.

      Bisa juga dilakukan melalui stored procedure dengan cara kerja sama namun bekerja pada sisi mysql.

  • Jack

    Ajiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiip…………………………………

  • wiewin

    makacihh…, sangat membantu buat tugas akir saya yg btuhin generasi data,,, 🙂

  • miko

    thx gan sangat membantu

  • surya panggabean