Pivot Table Dinamis Dengan MySQL

Pada kesempatan kali ini kita akan membahas tentang pivot table dinamis dengan MySQL.

Tutorial ini merupakan kelanjutan dari tutorial sebelumnya yang berjudul  Pivot Table Dengan MySQL.

Pada tutorial tersebut, kita telah membahas bagaimana membuat pivot table murni dengan SQL, pivot table tersebut hanya dapat digunakan untuk bentuk kolom yang statis.

Pada kondisi tertentu, data yang akan kita gunakan sebagai kolom berubah ubah, sehingga banyaknya kolom pada pivot table juga ikut berubah ubah, oleh karena itu SQL pada pivot table statis sudah tidak dapat kita gunakan lagi.

I.  Persiapan DATA

Karena tutorial ini merupakan lanjutan dari tutorial sebelumnya, maka data yang kita gunakan juga sama seperti  data sebelumnya, yaitu seperti tabel dibawah ini:

Sobat dapat mendownload file SQL nya yang dapat didump menggunakan aplikasi database manager seperti phpMyAdmin, Heidi SQL atau Toad for MySQL

II.  Pivot Table Dinamis Dengan MySQL

Selanjutnya, mari kita buat pivot table dinamis dengan MySQL. Untuk membuat pivot table dinamis, kita harus membuat SQL secara dinamis.

Karena SQL bukanlah bahasa dinamis (seperti penggunaan loop pada PHP – for, while, dll), maka kita tidak bisa membuat perintah SQL secara dinamis begitu saja.

Untuk itu, kita perlu “mengakalinya”, caranya, kita membuat perintah SQL dengan seolah-olah kita akan mengambil data.

Adapun query jadinya adalah sebagai berikut:

SET @sql_dinamis = (
		SELECT
			GROUP_CONCAT( DISTINCT
				CONCAT('SUM( IF(MONTH(tgl_trx) = '
					, MONTH(tgl_trx)
					, ',nilai_trx,0) ) AS bln_'
					, MONTH(tgl_trx)
				)
			)
		FROM tabel_sales
	);

SET @SQL = CONCAT('SELECT nama, ', 
			  @sql_dinamis, ' 
		   FROM tabel_sales
		   GROUP BY nama WITH ROLLUP'
	   );

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Hasil yang kita peroleh adalah:

+---------+---------+---------+---------+
| nama    | bln_1   | bln_2   | bln_3   |
+---------+---------+---------+---------+
| Alfa    |  375000 |  815000 |  375000 |
| Bravo   |  310000 |  525000 |  825000 |
| Charlie |  850000 |  300000 |  725000 |
| Delta   |       0 | 1000000 |  370000 |
| NULL    | 1535000 | 2640000 | 2295000 |
+---------+---------+---------+---------+

Penjelasan:

Pada query diatas, jumlah kolom akan terus bertambah jika ada tambahan transaksi di bulan April, Mei, Juni, dst… Jika digambarkan dalam bentuk SQL secara utuh, query diatas akan berbentuk seperti ini:

SELECT  nama,
	SUM( IF(MONTH(tgl_trx) = 1, nilai_trx,0) ) AS bln_1,
	SUM( IF(MONTH(tgl_trx) = 2, nilai_trx,0) ) AS bln_2,
	SUM( IF(MONTH(tgl_trx) = 3, nilai_trx,0) ) AS bln_3
FROM tabel_sales
GROUP BY nama

Agar lebih mudah memahami bagaimana membut query secara dinamis,  berikut beberapa hal yang dapat dijadikan pedoman:

1 Tentukan bagian SQL yang bersifat dinamis

Langkah pertama adalah buat query jadinya, kemudian identifikasi bagian query yang bersifat dinamis, pada contoh diatas, bagian dinamis adalah baris 2 s.d 4, sebagai berikut:

SELECT  nama,
	SUM( IF(MONTH(tgl_trx) = 1, nilai_trx,0) ) AS bln_1,
	SUM( IF(MONTH(tgl_trx) = 2, nilai_trx,0) ) AS bln_2,
	SUM( IF(MONTH(tgl_trx) = 3, nilai_trx,0) ) AS bln_3
FROM tabel_sales
GROUP BY nama

Selanjutnya, kita susun query sedemikian rupa sehingga menghasilkan query seperti pada baris 2 s.d 4 diatas, kemudian simpan hasilnya pada suatu variable.

SET @sql_dinamis = (
	SELECT
		GROUP_CONCAT( DISTINCT
			CONCAT('SUM( IF(MONTH(tgl_trx) = '
				, MONTH(tgl_trx)
				, ',nilai_trx,0) ) AS bln_'
				, MONTH(tgl_trx)
			)
		)
	FROM tabel_sales
);

Pada contoh diatas, kita simpan query ke dalam variabel @sql_dinamis.

Pada query diatas, query utamanya adalah:

CONCAT('SUM( IF(MONTH(tgl_trx) = '
	, MONTH(tgl_trx)
	, ',nilai_trx,0) ) AS bln_'
	, MONTH(tgl_trx)
)

Query tersebut akan menghasilkan:

+----------------------------------------------------+
| query                                              |
+----------------------------------------------------+
| SUM( IF(MONTH(tgl_trx) = 1,nilai_trx,0) ) AS bln_1 |
| SUM( IF(MONTH(tgl_trx) = 1,nilai_trx,0) ) AS bln_1 |
| SUM( IF(MONTH(tgl_trx) = 1,nilai_trx,0) ) AS bln_1 |
| SUM( IF(MONTH(tgl_trx) = 2,nilai_trx,0) ) AS bln_2 |
| SUM( IF(MONTH(tgl_trx) = 1,nilai_trx,0) ) AS bln_1 |
| SUM( IF(MONTH(tgl_trx) = 1,nilai_trx,0) ) AS bln_1 |
| SUM( IF(MONTH(tgl_trx) = 2,nilai_trx,0) ) AS bln_2 |
| SUM( IF(MONTH(tgl_trx) = 3,nilai_trx,0) ) AS bln_3 |
| SUM( IF(MONTH(tgl_trx) = 2,nilai_trx,0) ) AS bln_2 |
| SUM( IF(MONTH(tgl_trx) = 2,nilai_trx,0) ) AS bln_2 |
| SUM( IF(MONTH(tgl_trx) = 2,nilai_trx,0) ) AS bln_2 |
| SUM( IF(MONTH(tgl_trx) = 3,nilai_trx,0) ) AS bln_3 |
| SUM( IF(MONTH(tgl_trx) = 3,nilai_trx,0) ) AS bln_3 |
| SUM( IF(MONTH(tgl_trx) = 3,nilai_trx,0) ) AS bln_3 |
| SUM( IF(MONTH(tgl_trx) = 3,nilai_trx,0) ) AS bln_3 |
| SUM( IF(MONTH(tgl_trx) = 1,nilai_trx,0) ) AS bln_1 |
| SUM( IF(MONTH(tgl_trx) = 2,nilai_trx,0) ) AS bln_2 |
| SUM( IF(MONTH(tgl_trx) = 3,nilai_trx,0) ) AS bln_3 |
| SUM( IF(MONTH(tgl_trx) = 3,nilai_trx,0) ) AS bln_3 |
| SUM( IF(MONTH(tgl_trx) = 2,nilai_trx,0) ) AS bln_2 |
| SUM( IF(MONTH(tgl_trx) = 2,nilai_trx,0) ) AS bln_2 |
| SUM( IF(MONTH(tgl_trx) = 3,nilai_trx,0) ) AS bln_3 |
+----------------------------------------------------+

Selanjutnya, kita gunakan klausa DISTINCT untuk menghilangkan data yang sama:

DISTINCT CONCAT('SUM( IF(MONTH(tgl_trx) = '
	, MONTH(tgl_trx)
	, ',nilai_trx,0) ) AS bln_'
	, MONTH(tgl_trx)
)

Hasil yang kita peroleh:

+----------------------------------------------------+
| query                                              |
+----------------------------------------------------+
| SUM( IF(MONTH(tgl_trx) = 1,nilai_trx,0) ) AS bln_1 |
| SUM( IF(MONTH(tgl_trx) = 2,nilai_trx,0) ) AS bln_2 |
| SUM( IF(MONTH(tgl_trx) = 3,nilai_trx,0) ) AS bln_3 |
+----------------------------------------------------+

Terakhir, kita gunakan GROUP_CONCAT untuk menggabungkan semua baris dengan pemisah tanda koma (,)

GROUP_CONCAT( DISTINCT
	CONCAT('SUM( IF(MONTH(tgl_trx) = '
		, MONTH(tgl_trx)
		, ',nilai_trx,0) ) AS bln_'
		, MONTH(tgl_trx)
	)
)

Hasil yang kita peroleh:

+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| query                                                                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| SUM( IF(MONTH(tgl_trx) = 1,nilai_trx,0) ) AS bln_1,SUM( IF(MONTH(tgl_trx) = 2,nilai_trx,0) ) AS bln_2,SUM( IF(MONTH(tgl_trx) = 3,nilai_trx,0) ) AS bln_3 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+

Untuk memastikan hasil telah sesuai yang kita harapkan, tes isi dari variabel @sql_dinamis. Jalankan perintah:

SELECT @sql_dinamis;

Hasil:

+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @sql_dinamis                                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  SUM( IF(MONTH(tgl_trx) = 1,nilai_trx,0) ) AS bln_1, SUM( IF(MONTH(tgl_trx) = 2,nilai_trx,0) ) AS bln_2, SUM( IF(MONTH(tgl_trx) = 3,nilai_trx,0) ) AS bln_3 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+

Hasil tersebut telah sesuai dengan yang kita harapkan.

2 Gabungkan Query Statis Dengan Dinamis

Setelah kita simpan query dinamis pada suatu variable, selanjutnya, kita gabungkan query dinamis tersebut dengan query yang lain sehingga menjadi query yang utuh, hasilnya kita simpan pada suatu variabel:

SET @SQL = CONCAT('SELECT nama, ', 
			  @sql_dinamis, ' 
		   FROM tabel_sales
		   GROUP BY nama WITH ROLLUP'
	   );

Pada contoh diatas, kita simpan keseluruhan query kedalam variabel @sql

Cek apakah isi dari variabel @sql telah sesuai dengan yang kita harapkan, jalankan perintah:

SELECT @sql;

Hasil yang kita peroleh:

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @sql                                                                                                                                                                                                 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT nama, SUM( IF(MONTH(tgl_trx) = 1,nilai_trx,0) ) AS bln_1,SUM( IF(MONTH(tgl_trx) = 2,nilai_trx,0) ) AS bln_2,SUM( IF(MONTH(tgl_trx) = 3,nilai_trx,0) ) AS bln_3 FROM tabel_sales
GROUP BY nama |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Hasil diatas telah sesuai dengan yang kita harapkan.

3 Eksekusi Query

Setelah Query kita susun dengan lengkap, terakhir, kita jalankan query tersebut. Karena query disimpan dalam bentuk variable, kita tidak dapat langsung mengeksekusinya, melainkan harus melalui statemen PREPARE

Query yang kita jalankan adalah:

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Pada query diatas, kita beri nama statement PREPARE dengan nama stmt yang merupakan kependekan dari statement, sobat bebas menggunakan nama lain.

Dengan klausa FROM, statemen PREPARE tersebut kita isi dengan query yang telah kita simpan pada variabel @sql

Selanjutnya, kita jalankan statemen EXECUTE untuk menjalankan SQL yang ada di statemen PREPARE. Pada contoh diatas, kita eksekusi SQL yang ada di dalam  stmt.

Pada tahap ini, MySQL akan menampilkan hasil sesuai dengan yang kita harapkan.

Sebagai tambahan, kita dapat menjalankan statemen DEALLOCATE  PREPARE stmt yang akan melepas (merelease) atau menghapus statemen PREPARE yang bernama stmt

Hal ini bertujuan untuk mengurangi batas maksimal statemen PREPARE yang dapat disimpan.

III.  Pivot Table Dinamis Dengan MySQL – Menambahkan Kolom  Baru

Setelah sobat memahami bagaimana membuat pivot table dinamis dengan MySQL, selanjutnya mari kita buat variasi SQL dengan:

• Menambahkan kolom jumlah transaksi yang berisi banyaknya transaksi per bulan, per nama petugas.
• Menambahkan kolom TOTAL yang berisi nilai total dari masing masing nama sales.
• Mengubah nilai NULL pada kolom nama menjadi TOTAL. Karena kita menggunakan klausa WITH ROLLUP, maka kolom nama pada baris total berisi nilai NULL, agar lebih mudah dibaca, kita ganti nilai NULL tersebut dengan kata-kata TOTAL.

Query yang kita jalankan:

SET @sql_dinamis = (
		SELECT
			GROUP_CONCAT( DISTINCT
				CONCAT('COUNT( IF(MONTH(tgl_trx) = '
					, MONTH(tgl_trx)
					, ', nilai_trx, NULL) ) AS trx_'
					, MONTH(tgl_trx)
					, ', SUM( IF(MONTH(tgl_trx) = '
					, MONTH(tgl_trx)
					, ', nilai_trx, 0) ) AS bln_'
					, MONTH(tgl_trx)
				)
			)
		FROM tabel_sales
	);

SET @SQL = CONCAT('SELECT IFNULL(nama, "TOTAL") AS nama_sales, ', 
			  @sql_dinamis, ', 
			  COUNT(tgl_trx) AS JML_TRX,
			  SUM(nilai_trx) AS TOTAL 
		   FROM tabel_sales
		   GROUP BY nama 
		   WITH ROLLUP'
	);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Hasil yang kita peroleh adalah:

+------------+---------+-------+---------+-------+---------+-------+---------+---------+
| nama_sales | bln_1   | trx_1 | bln_2   | trx_2 | bln_3   | trx_3 | JML_TRX | TOTAL   |
+------------+---------+-------+---------+-------+---------+-------+---------+---------+
| Alfa       |  375000 |     2 |  815000 |     3 |  375000 |     2 |       7 | 1565000 |
| Bravo      |  310000 |     1 |  525000 |     2 |  825000 |     3 |       6 | 1660000 |
| Charlie    |  850000 |     3 |  300000 |     1 |  725000 |     2 |       6 | 1875000 |
| Delta      |       0 |     0 | 1000000 |     2 |  370000 |     1 |       3 | 1370000 |
| TOTAL      | 1535000 |     6 | 2640000 |     8 | 2295000 |     8 |      22 | 6470000 |
+------------+---------+-------+---------+-------+---------+-------+---------+---------+

Penjelasan:

• Pada variabel @sql_dinamis, kita tambahkan fungsi COUNT untuk menghitung jumlah baris pada kolom tgl_trx (baris 4-7).
• Pada variabel @sql, kita tambahkan IFNULL(nama, "TOTAL") AS nama_sales  (baris 17) untuk mengubah nilai NULL  pada kolom nama_sales menjadi TOTAL
• Kita tambahkan COUNT(tgl_trx) AS JML_TRX (baris 19) untuk menambahkan kolom JML_TRX yang berada di bagian kanan.
• Terakhir, kita tambahkan  SUM(nilai_trx) AS TOTAL  (baris 20) untuk menambahkan kolom  TOTAL yang berada di bagian paling kanan.

Jika digambarkan dalam query yang utuh, query diatas akan berbentuk seperti ini:

SELECT  IFNULL(nama, "TOTAL") AS nama_sales, 
	COUNT( IF(MONTH(tgl_trx) = 1, nilai_trx, NULL) ) AS trx_1, 
	SUM( IF(MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS bln_1,
	COUNT( IF(MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
	SUM( IF(MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS bln_2,
	COUNT( IF(MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
	SUM( IF(MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS bln_3, 
	COUNT(tgl_trx) AS JML_TRX,
	SUM(nilai_trx) AS TOTAL 
FROM tabel_sales
GROUP BY nama 
WITH ROLLUP

Dalam praktik, kondisinya bisa bermacam macam, query diatas dapat digunakan sebagai contoh/inspirasi dalam membuat berbagai jenis query pivot table dinamis sesuai kondisi yang ada.

IV. Kesimpulan

Meskipun SQL bukanlah bahasa dinamis, namun dengan sedikit jalan “memutar”, kita dapat membuat pivot table dinamis dengan MySQL.

Intinya, untuk membuat pivot table tersebut, kita tentukan terlebih dahulu mana query yang bersifat dinamis, selanjutnya lengkapi query dinamis tersebut dengan query statis lainnya, sehingga menghasilkan query yang utuh.

Demikian pembahasan tentang pivot table dinamis dengan MySQL, semoga bermanfaat.