Crosstab Query di MySQL

Ada sebuah fitur MS Access yang tidak saya temukan di MySQL ketika sedang asyik bereksperimen dengan proyek kecil ini. Fitur itu adalah kemudahan menyajikan data dalam bentuk crosstab, singkatan dari cross tabulation, yaitu sebuah wizard untuk menampilkan data statistikal secara mudah. MS Access melalui fitur Crosstab Query Wizard dan juga fitur PivotTable pada MS Excel bisa menyajikan simpulan data seperti yang tersaji di tautan Wikipedia di atas dengan cepat.


Sebenarnya MySQL pun bisa juga menyajikan data seperti itu, namun kebanyakan fitur itu hanya terdapat pada aplikasi query designer yang biasanya berharga mahal. Untung saja saya menemukan artikel bagus mengenai crosstab ini, yakni menampilkan crosstab dengan memanfaatkan SELECT query dengan SUM dan COUNT, bahkan bisa juga direlasikan dengan tabel lain menggunakan JOIN.

DBMS seperti halnya MySQL memang dioptimasikan untuk penyimpanan data yang normalized sehingga data dalam tabel bersifat unik dan tidak terdapat redundansi data. Namun dalam penyajiannya, misalnya untuk pembuatan laporan, data unik tersebut susah untuk dibaca karena tersaji dalam banyak baris — ratusan bahkan ribuan baris dalam kasus saya. Untuk mempermudah pembacaannya, maka dibuatlah simpulan statistik dengan crosstab tersebut.

Untuk sederhananya saya ambilkan contoh data dari Wikipedia di atas:

Data Mentah dalam Tabel MySQL (sample_table)
sample_no	gender	handedness
1	Female	right-handed
2	Male	left-handed
3	Male	right-handed
4	Female	right-handed
5	Female	right-handed
6	Male	right-handed
7	Male	left-handed
8	Male	right-handed
9	Female	right-handed
10	Female	left-handed
11	Male	right-handed
12	Female	right-handed

Untuk menyajikan rangkuman data di atas, kita bisa melakukannya dengan query berikut:

SELECT gender, SUM(IF(handedness='right-handed',1,0)) AS "Right-handed",
        SUM(IF(handedness='left-handed',1,0)) AS "Left-handed",
        COUNT(*) AS TOTALS
        FROM sample_table
        GROUP BY gender;

Hasilnya akan seperti ini:

Perintah SELECT untuk menampilkan tabel crosstab

Pada contoh query di atas ketika MySQL melakukan perintah SELECT, dia memeriksa kondisi handedness, jika kondisinya right-handed maka MySQL akan menambahkan 1 pada kolom Right-handed dan 0 pada kolom Left-handed (SUM(IF(handedness='right-handed',1,0)) AS "Right-handed",),begitu pula sebaliknya bila kondisinya left-handed. Selanjutnya kita bisa menyimpan query ini sebagai view ke dalam MySQL dengan perintah CREATE VIEW, misalnya view tersebut kita namakan xtab:

CREATE VIEW xtab AS SELECT gender, SUM(IF(handedness='right-handed',1,0)) AS "Right-handed",
        SUM(IF(handedness='left-handed',1,0)) AS "Left-handed",
        COUNT(*) AS TOTALS
        FROM sample_table
        GROUP BY gender;

Untuk menampilkannya kembali, kita tinggal menggunakan perintah SELECT, seperti ini:

Perintah SELECT terhadap view xtab

Begitulah cara sederhana untuk membuat tabel crosstab di MySQL. Untuk lebih lanjutnya saya sarankan untuk membaca artikel ini. Di situ dijelaskan juga bagaimana menggunakan query crosstab dengan JOIN dan USING.