Dynamic CrossTab di MySQL

Membuat report adalah salah satu pekerjaan yang cukup rumit, lebih lagi jika harus membuat report crosstab dengan jumlah kolom yang dinamis dijamin pusing tujuh keliling. Mencoba menjawab sejumlah pertanyaan tentang crosstab dinamis yang muncul di Facebook grup PHP Indonesia dan MySQL Indonesia, akhirnya saya penasaran juga....

Misalkan kita punya data di tabel stok seperti ini:

KodeGudang KodeProduk Stok
Gd1        Produk1       5
Gd1        Produk2      10
Gd1        Produk3      15
Gd2        Produk1      20
Gd2        Produk2      25
Gd3        Produk3      30

Namun kita perlu membuat laporan seperti ini

KodeProduk Gd1 Gd2 Gd3 
Produk1      5  20   0 
Produk2     10  25   0 
Produk3     15   0  30

Kolom Gd1 sampai Gd3 sifatnya dinamis, artinya KodeGudang bisa jadi tidak hanya 3, bisa lebih banyak, bisa juga lebih sedikit.

Solusinya adalah memanfaatkan stored procedure di MySQL, dan aplikasi PHP kita cukup terima data mateng hasil perhitungan stored procedure bersangkutan, dan Alhamdulillah berhasil menemukan solusi seperti di bawah ini. Silakan dipelajari :D

D:\xampp\mysql\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed

mysql> select * from mydata;
+-------+------------+-------+
| kode  | tanggal    | nilai |
+-------+------------+-------+
| CN235 | 2012-01-02 |   100 |
| CN235 | 2012-01-05 |   120 |
| CN235 | 2012-01-12 |   140 |
| CN235 | 2012-01-11 |   150 |
| CN236 | 2012-01-11 |   200 |
| CN235 | 2012-01-15 |   160 |
| CN235 | 2012-01-17 |   170 |
| CN235 | 2012-01-16 |   180 |
| CN236 | 2012-01-14 |   200 |
| CN236 | 2012-01-12 |   400 |
| CN236 | 2012-01-15 |   120 |
| CN236 | 2012-01-17 |   400 |
| CN236 | 2012-01-18 |   600 |
| CN236 | 2012-01-05 |   800 |
+-------+------------+-------+
14 rows in set (0.00 sec)

mysql> DELIMITER $$

mysql> DROP PROCEDURE IF EXISTS `test`.`mysp`$$
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE `test`.`mysp`()
    -> BEGIN
    ->  -- declare variables
    ->  declare prepsql varchar(1000);
    ->  declare no_more_rows boolean default false;
    ->  declare tanggal_val varchar(20);
    ->  declare tanggal_cur cursor for
    ->          select distinct tanggal
    ->          from mydata order by tanggal;
    ->  declare continue handler for not found
    ->          set no_more_rows = true;
    ->
    ->  -- start generating sql
    ->  set prepsql = 'select kode';
    ->  open tanggal_cur;
    ->  the_loop: loop
    ->      -- fetch data
    ->      fetch tanggal_cur
    ->      into tanggal_val;
    ->      -- break out loop
    ->      if no_more_rows then
    ->              close tanggal_cur;
    ->              leave the_loop;
    ->      end if;
    ->      -- display
    ->      set prepsql = concat(prepsql,'\n, sum(case when tanggal=\'',tanggal_val,'\' then ifnull(nilai,0) else 0 end) "',tanggal_val,'"');
    ->  end loop the_loop;
    ->  set prepsql = concat(prepsql,'\nfrom mydata group by kode');
    ->
    ->  -- executing generated query
    ->  set @mysql = prepsql;
    ->  prepare stmt from @mysql;
    ->  execute stmt;
    ->
    ->  -- clean up
    ->  drop prepare stmt;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call mysp;
+-------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
| kode  | 2012-01-02 | 2012-01-05 | 2012-01-11 | 2012-01-12 | 2012-01-14 | 2012-01-15 | 2012-01-16 | 2012-01-17 | 2012-01-18 |
+-------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
| CN235 |        100 |        120 |        150 |        140 |          0 |        160 |        180 |        170 |          0 |
| CN236 |          0 |        800 |        200 |        400 |        200 |        120 |          0 |        400 |        600 |
+-------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.04 sec)

mysql>

Source code juga bisa dilihat di sini

Secara umum alur dari stored procedure di atas adalah:

1. query ke tabel data untuk mendapatkan kolom crosstab
2. susun sql statement berdasarkan hasil query tersebut
3. siapkan sql menggunakan perintah prepare
4. jalankan query

Dengan cara di atas, aplikasi hanya perlu memanggil stored procedure dan langsung mendapatkan hasil report-nya dalam bentuk "matang" tanpa perlu proses perhitungan lagi.

Selamat mencoba... :)