Query Crosstab di MySQL

Buat: 08/24/2013 - 11:43

Belajar SQL | Crosstab atau Pivot Table adalah salah satu teknik membuat laporan yang sangat diperlukan. Inti dari teknik ini adalah mengubah row dari hasil query database menjadi kolom. Bagi teman-teman yang baru mempelajari SQL dasar saja biasanya akan mencari solusi dengan memanfaatkan coding PHP dan looping untuk menyelesaikannya. Namun dengan menggunakan SQL, kita bisa melakukannya dalam satu langkah saja.

Sebagai contoh, misalkan kita mempunyai table dengan struktur dan data seperti ini:

+----+-------+-----------+
| ID | NOPEL | KATEGORI  |
+----+-------+-----------+
| 1  | 001   | INQUIRY   |
| 2  | 002   | COMPLAINT |
| 3  | 003   | REQUEST   |
| 4  | 004   | INQUIRY   |
| 5  | 005   | REQUEST   |
| 6  | 001   | COMPLAINT |
| 7  | 001   | REQUEST   |
| 8  | 003   | REQUEST   |
| 9  | 004   | INQUIRY   |
+----+-------+-----------+

Kemudian kita ingin menampilkan hasilnya menjadi seperti ini

+-------+---------+---------+-----------+-------+
| nopel | inquiry | request | complaint | total |
+-------+---------+---------+-----------+-------+
| 001   |       1 |       1 |         1 |     3 |
| 002   |       0 |       0 |         1 |     1 |
| 003   |       0 |       2 |         0 |     2 |
| 004   |       2 |       0 |         0 |     2 |
| 005   |       0 |       1 |         0 |     1 |
+-------+---------+---------+-----------+-------+

Caranya adalah dengan memanfaatkan logika percabangan di SQL menggunakan CASE clause dan fungsi aggregate SUM() dan GROUP BY. Logikanya adalah, ketika kita ingin menghitung jumlah INQUIRY, maka gunakan CASE clause untuk mendeteksi nilai dari  field KATEGORI, dan jika isi fieldnya adalah 'INQUIRY' maka berikan nilai 1, dan jika bukan maka berikan nilai 0. setelah itu jumlahkan hasilnya menggunakan SUM(). Hal yang sama dilakukan untuk nilai-nilai kategori lainnya, sehingga kita bisa dapatkan query seperti ini.

select nopel
, sum(case when kategori='INQUIRY' then 1 else 0 end) inquiry
, sum(case when kategori='REQUEST' then 1 else 0 end) request
, sum(case when kategori='COMPLAINT' then 1 else 0 end) complaint
, sum(case when kategori='INQUIRY' then 1 else 0 end) 
+ sum(case when kategori='REQUEST' then 1 else 0 end) 
+ sum(case when kategori='COMPLAINT' then 1 else 0 end) total
from tbl_data
group by nopel ;

Belajar SQL mudah kan?

Screenshot lengkap query juga dapat dilihat di sini. Sedangkan untuk query crosstab yang dinamis menggunakan Stored Procedure dapat dilihat di artikel ini.

^_^

About Author

Tentang PHP Indonesia

PHP Indonesia adalah sebuah komunitas untuk semua orang yang mencintai PHP. Fokus utama kami adalah pemrograman menggunakan bahasa PHP, namun tidak menutup kemungkinan pembahasan yang mencakup keseluruhan LAMP stack. Topik bisa dari pemrograman PHP, penanganan memcached, optimasi database, konfigurasi server, tuning server web, application deployment, framework PHP, hosting pilihan dan sebagainya.

Petunjuk Penulisan

Berikut ini adalah beberapa petunjuk penulisan artikel. Petunjuk dibuat bukan untuk membatasi tetapi demi menjaga kualitas artikel yang terpublikasi di situs ini. Selain itu, keseragaman gaya bahasa dapat menjaga readibilitas (tingkat keterbacaan) sehingga pembaca akan lebih nyaman dalam membaca artikel di situs ini.