ffCCBot/2.0 (http://commoncrawl.org/faq/)
Update: 08-12-2016

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:

id_trx nama tgl_trx tim nilai_trx
1 Alfa 2016-01-10 1 250000
2 Charlie 2016-01-02 2 175000
3 Bravo 2016-01-01 1 310000
4 Bravo 2016-02-04 1 250000
5 Alfa 2016-01-15 1 300000
6 Charlie 2016-01-13 2 325000
7 Bravo 2016-02-07 1 275000
8 Bravo 2016-03-06 1 150000
9 Alfa 2016-02-05 1 215000
10 Alfa 2016-02-22 1 350000
11 Alfa 2016-02-02 1 450000
12 Alfa 2016-03-12 1 150000
13 Alfa 2016-03-17 1 225000
14 Bravo 2016-03-11 1 150000
15 Bravo 2016-03-18 1 150000
16 Charlie 2016-01-23 2 350000
17 Charlie 2016-02-22 2 300000
18 Charlie 2016-03-21 2 275000
19 Charlie 2016-03-12 2 450000
20 Delta 2016-02-11 2 450000
21 Delta 2016-02-17 2 550000
22 Delta 2016-03-11 2 370000

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.

Recomended Post

6 Feedback dari pembaca

  • pagi…mau tanya mas…
    Kalo misalkan dari data diatas ingin menampilkan nama barang yg terjual tiap sales, kemudian nama sales munculnya satu kali dikolom sales (seperti merge cells/rowspan di html)sedangkan barang nya muncul sesuai nama barang yg terjual di field kolom….
    terimakasih

    • Kebetulan di contoh diatas per id_transaksi mas, tidak per barang, sebagai alternatif, saya buatkan perbulan ya mas, mungkin bisa menjadi inspirasi.
      Query statisnya adalah:

      SELECT  MONTH(tgl_trx) AS bulan,
      	COUNT(IF(nama="Alfa", tgL_trx, NULL)) AS Alfa,
      	COUNT(IF(nama="Bravo", tgL_trx, NULL)) AS Bravo,
      	COUNT(IF(nama="Charlie", tgL_trx, NULL)) AS Charlie,
      	COUNT(IF(nama="Delta", tgL_trx, NULL)) AS Delta
      FROM tabel_sales
      GROUP BY MONTH(tgl_trx)
      WITH ROLLUP

      Hasil:

      +-------+------+-------+---------+-------+
      | bulan | Alfa | Bravo | Charlie | Delta |
      +-------+------+-------+---------+-------+
      |     1 |    2 |     1 |       3 |     0 |
      |     2 |    3 |     2 |       1 |     2 |
      |     3 |    2 |     3 |       2 |     1 |
      |  NULL |    7 |     6 |       6 |     3 |
      +-------+------+-------+---------+-------+

      Query Dinamisnya:

      SET @sql_dinamis = (
      		SELECT
      			GROUP_CONCAT( DISTINCT
      				CONCAT('COUNT( IF(nama = "'
      					, nama
      					, '" ,tgl_trx, NULL) ) AS '
      					, nama
      				)
      			)
      		FROM tabel_sales_backup
      	);
      
      SET @SQL = CONCAT('SELECT MONTH(tgl_trx) AS Bulan, ', 
      			  @sql_dinamis, ' 
      		   FROM tabel_sales_backup
      		   GROUP BY MONTH(tgl_trx)
      			WITH ROLLUP'
      	   );
      
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;

  • pagi pak, mau tanya saya punya table data keberangkatan dengan field nama_pt,negara_tujuan

    nama_pt HONG KONG TAIWAN MALAYSIA
    GRAHA MITRA 6 9 1
    INSAN MANDIRI 7 0 3

      • maaf pak, kurang jelas ya? jadi saya punya tabel keberangkatan
        keberangkatan
        id_keberangkatan
        nama_pt
        negara_tujuan

        yang diinginkan
        +————+———+——+——+——-+
        nama pt | malaysia| singapura| hongkong|Jumlah
        +————+——-+——+——+——-+
        A |2 | 0 | 1 | 3 |
        B |1 | 3 | 0 | 4 |
        C | 0 | 0 | 2 | 2 |

        oh ya terus cara implementasiin di php bagaimana ya?

        • Query statisnya seperti ini mas:

          SELECT  nama_pt, 
          	COUNT(IF(negara_tujuan = 'malaysia', nama_pt, NULL)) AS malaysia, 
          	COUNT(IF(negara_tujuan = 'singapura', nama_pt, NULL)) AS singapura, 
          	COUNT(IF(negara_tujuan = 'hongkong', nama_pt, NULL)) AS hongkong
          FROM keberangkatan
          GROUP BY nama_pt

          Untuk query dinamisnya bisa dicoba seperti ini mas:

          SET @sql_dinamis = (
          		SELECT
          			GROUP_CONCAT( DISTINCT
          				CONCAT('COUNT( IF(negara_tujuan = "'
          					, negara_tujuan
          					, '" ,nama_pt, NULL) ) AS '
          					, negara_tujuan
          				)
          			)
          		FROM keberangkatan
          	);
          
          SET @SQL = CONCAT('SELECT nama_pt, ', 
          			  @sql_dinamis, ' 
          		   FROM keberangkatan
          		   GROUP BY nama_pt'
          	   );
          
          PREPARE stmt FROM @sql;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;

          Untuk implementasinya pada PHP bisa dipelajari tutorial ini mas: Pivot Table Dengan PHP dan MySQL

Silakan tinggalkan komentar

Like Us

Dapatkan update artikel terbaru via E-Mail
  1. Memahami dan Menampilkan Tanggal dan Waktu Pada PHP
  2. Menghitung Selisih Waktu Dengan PHP – Cara Termudah
  3. Memahami Fungsi Date Pada PHP
  4. Memahami Fungsi Time, Strtotime, dan Mktime Pada PHP – Memanipulasi Waktu
  5. Memahami Zona Waktu (Timezone) dan Selisih Waktu Pada PHP
  6. Setting ODBC MySQL Untuk Gammu
  7. Cara Install dan Menggunakan Gammu di Windows
  8. Format Tanggal Indonesia Dengan PHP
  9. Cara Install MySQL Portable Zip File di Windows – Panduan Lengkap
  10. Memahami Statemen Return Pada PHP