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. Laporan crosstab ini adalah laporan yang mengolah data mentah berupa baris-baris menjadi kolom-kolom.
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 transaksi seperti tabel di bawah ini:
+-------+------------+-------+
| kode | tanggal | nilai |
+-------+------------+-------+
| CN235 | 2012-01-02 | 100 |
| CN235 | 2012-01-11 | 140 |
| CN235 | 2012-01-11 | 150 |
| CN235 | 2012-01-15 | 160 |
| CN235 | 2012-01-17 | 180 |
| CN235 | 2012-01-17 | 170 |
| CN236 | 2012-01-02 | 800 |
| CN236 | 2012-01-11 | 200 |
| CN236 | 2012-01-11 | 400 |
| CN236 | 2012-01-15 | 120 |
| CN236 | 2012-01-17 | 400 |
| N2130 | 2012-01-02 | 120 |
| N2130 | 2012-01-15 | 200 |
| N2130 | 2012-01-17 | 600 |
+-------+------------+-------+
Namun kita perlu membuat laporan seperti ini
+-------+------------+------------+------------+------------+-------+
| kode | 2012-01-02 | 2012-01-11 | 2012-01-15 | 2012-01-17 | total |
+-------+------------+------------+------------+------------+-------+
| CN235 | 100 | 290 | 160 | 350 | 900 |
| CN236 | 800 | 600 | 120 | 400 | 1920 |
| N2130 | 120 | 0 | 200 | 600 | 920 |
+-------+------------+------------+------------+------------+-------+
Kolom 2012-01-02 sampai 2012-01-17 sifatnya dinamis, artinya kolom tanggal bisa jadi tidak hanya 4 kolom, bisa lebih banyak, dan bisa juga lebih sedikit, tergantung dari data yang ada.
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 😀
PS C:\Users\hiday> mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 26
Server version: 10.5.7-MariaDB-log mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use test
Database changed
MariaDB [test]> -- First drop sample table if already exists
MariaDB [test]> DROP TABLE if exists transaksi cascade;
Query OK, 0 rows affected (0.009 sec)
MariaDB [test]> -- Create our test table
MariaDB [test]> CREATE TABLE transaksi as
-> select 'CN235' kode, '2012-01-02' tanggal, 100 nilai union all
-> select 'N2130' kode, '2012-01-02' tanggal, 120 nilai union all
-> select 'CN235' kode, '2012-01-11' tanggal, 140 nilai union all
-> select 'CN235' kode, '2012-01-11' tanggal, 150 nilai union all
-> select 'CN236' kode, '2012-01-11' tanggal, 200 nilai union all
-> select 'CN235' kode, '2012-01-15' tanggal, 160 nilai union all
-> select 'CN235' kode, '2012-01-17' tanggal, 170 nilai union all
-> select 'CN235' kode, '2012-01-17' tanggal, 180 nilai union all
-> select 'N2130' kode, '2012-01-15' tanggal, 200 nilai union all
-> select 'CN236' kode, '2012-01-11' tanggal, 400 nilai union all
-> select 'CN236' kode, '2012-01-15' tanggal, 120 nilai union all
-> select 'CN236' kode, '2012-01-17' tanggal, 400 nilai union all
-> select 'N2130' kode, '2012-01-17' tanggal, 600 nilai union all
-> select 'CN236' kode, '2012-01-02' tanggal, 800 nilai
-> ;
Query OK, 14 rows affected (0.018 sec)
Records: 14 Duplicates: 0 Warnings: 0
MariaDB [test]> -- First drop if the procedure already exists
MariaDB [test]> DROP PROCEDURE if exists crosstab_transaksi;
Query OK, 0 rows affected (0.007 sec)
MariaDB [test]> -- Now create our stored procedure
MariaDB [test]> DELIMITER $$
MariaDB [test]> CREATE PROCEDURE crosstab_transaksi()
-> BEGIN
-> -- declare variables
-> declare prepsql varchar(1000);
-> declare summsql varchar(1000);
-> declare totalsql varchar(100);
-> -- generate summary clause
-> select group_concat(' sum(case when tanggal=\'',tanggal,'\' then nilai else 0 end) as "',tanggal,'"\n')
-> into summsql
-> from ( select distinct tanggal from transaksi order by tanggal ) tx;
-> -- generate full sql query
-> set totalsql = ', sum(nilai) as total\n';
-> set prepsql = concat('select kode,',summsql,totalsql,'from transaksi 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.007 sec)
MariaDB [test]> DELIMITER ;
MariaDB [test]> -- Let see our sample data
MariaDB [test]> SELECT * from transaksi
-> ORDER BY kode, tanggal;
+-------+------------+-------+
| kode | tanggal | nilai |
+-------+------------+-------+
| CN235 | 2012-01-02 | 100 |
| CN235 | 2012-01-11 | 140 |
| CN235 | 2012-01-11 | 150 |
| CN235 | 2012-01-15 | 160 |
| CN235 | 2012-01-17 | 180 |
| CN235 | 2012-01-17 | 170 |
| CN236 | 2012-01-02 | 800 |
| CN236 | 2012-01-11 | 200 |
| CN236 | 2012-01-11 | 400 |
| CN236 | 2012-01-15 | 120 |
| CN236 | 2012-01-17 | 400 |
| N2130 | 2012-01-02 | 120 |
| N2130 | 2012-01-15 | 200 |
| N2130 | 2012-01-17 | 600 |
+-------+------------+-------+
14 rows in set (0.001 sec)
MariaDB [test]> -- And then call our stored procedure to get the summary
MariaDB [test]> CALL crosstab_transaksi;
+-------+------------+------------+------------+------------+-------+
| kode | 2012-01-02 | 2012-01-11 | 2012-01-15 | 2012-01-17 | total |
+-------+------------+------------+------------+------------+-------+
| CN235 | 100 | 290 | 160 | 350 | 900 |
| CN236 | 800 | 600 | 120 | 400 | 1920 |
| N2130 | 120 | 0 | 200 | 600 | 920 |
+-------+------------+------------+------------+------------+-------+
3 rows in set (0.001 sec)
Query OK, 1 row affected (0.012 sec)
MariaDB [test]>
Secara umum alur dari stored procedure di atas adalah:
- Query ke tabel data untuk mendapatkan kolom crosstab.
- Susun sql statement berdasarkan hasil query tersebut
- Siapkan sql menggunakan perintah prepare
- Jalankan query.
Script yang diperlukan untuk membuat stored procedure nya adalah sebagai berikut,
-- Now create our stored procedure
DELIMITER $$
CREATE PROCEDURE crosstab_transaksi()
BEGIN
-- declare variables
declare prepsql varchar(1000);
declare summsql varchar(1000);
declare totalsql varchar(100);
-- generate summary clause
select group_concat(' sum(case when tanggal=\'',tanggal,'\' then nilai else 0 end) as "',tanggal,'"\n')
into summsql
from ( select distinct tanggal from transaksi order by tanggal ) tx;
-- generate full sql query
set totalsql = ', sum(nilai) as total\n';
set prepsql = concat('select kode,',summsql,totalsql,'from transaksi group by kode');
-- executing generated query
set @mysql = prepsql;
prepare stmt from @mysql;
execute stmt;
-- clean up
drop prepare stmt;
END$$
DELIMITER ;
Source code lengkap nya bisa dilihat di sini.
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… 🙂
