ffCCBot/2.0 (http://commoncrawl.org/faq/)
Update: 22-11-2016

Pivot Table Dengan MySQL – Panduan Lengkap

Pada kesempatan ini, kita akan membahas bagaimana cara membuat pivot table dengan MySQL.

Pivot tabel yang dimaksud disini sama seperti ketika kita membuat pivot table dengan Microsoft Excel, dimana kita membuat sumary dari suatu data dengan mengubah baris menjadi kolom.

Pada MySQL, kita tidak dapat langsung membuat pivot tabel seperti pada database lain yang memiliki fungsi PIVOT, melainkan kita harus mendefinisikan kolom satu per satu.

Hal ini sejalan dengan konsep bahwa SQL bukan bahasa dinamis yang bisa melakukan fungsi perulangan seperti bahasa pemrograman umumnya, melainkan bahasa statis dan tetap, sehingga dalam menuliskan query kita harus menulis secara manual.

Namun demikian untuk data dinamis, kita tetap dapat membuat query secara otomatis, namun perlu “jalan memutar” alias jalan tidak langsung,  pembahasan tentang topik ini cukup panjang, sehingga perlu pembahasan tersendiri.

I. Persiapan DATA

Baiklah, langsung saja, untuk dapat membuat pivot tabel dengan MySQL, tentu kita perlu suatu data, agar lebih mudah, kali ini kita akan menggunakan satu tabel saja yaitu tabel tabel_sales

Tabel tersebut terdiri dari 5 kolom yaitu, id_trx, nama, tgl_trx, tim, dan nilai_trx. Isi dari tabel tersebut tampak seperti tabel berikut:

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

Tabel diatas sudah cukup informatif, namun masih dapat dibuat lebih informatif lagi, terutama ketika membandingkan data secara horizontal.

II. Pivot Table Dengan MySQL

Pada pivot table kali ini, kita akan menampilkan data penjualan per bulan per nama petugas. Adapun query yang kita perlukan adalah sebagai berikut:

SELECT  nama,
	SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,
	SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,
	SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,
	SUM( nilai_trx ) AS total_trx
FROM tabel_sales
GROUP BY nama

Hasil yang kita peroleh adalah:

+---------+---------+----------+--------+-----------+
| nama    | januari | februari | maret  | total_trx |
+---------+---------+----------+--------+-----------+
| Alfa    |  550000 |  1015000 | 375000 |   1940000 |
| Bravo   |  310000 |   525000 | 450000 |   1285000 |
| Charlie |  850000 |   300000 | 725000 |   1875000 |
| Delta   |       0 |  1000000 | 370000 |   1370000 |
+---------+---------+----------+--------+-----------+

Tabel yang dihasilkan jauh lebih informatif dibanding dengan tabel aslinya, kita dapat membandingkan dengan mudah data penjualan per bulan.

Pada query diatas kita menggunakan fungsi SUM untuk menjumlahkan data per kolom, lebih jauh tentang fungsi ini dapat dibaca pada artikel: Menggunakan SUM IF untuk menjumlahkan nilai field/kolom suatu Tabel pada MySQL

Selain itu, kita juga menggunakan fungsi MONTH untuk mengambil data bulan pada kolom tgl_trx, jika hasil yang diperoleh angka 1, maka kita masukkan ke kolom januari, jika 2 ke februari, dan jika 3 ke kolom maret, selanjutnya kita gunakan fungsi SUM(nilai_trx) untuk membuat kolom total.

Melengkapi Tampilan Data

Agar lebih informatif, mari kita tambahkan data jumlah transaksi. Kita ubah query menjadi seperti ini:

SELECT  
	nama,
	SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,
	COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,
	SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,
	COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
	SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,
	COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
	
	COUNT(id_trx) AS jml_trx,
	SUM( nilai_trx ) AS total_trx
FROM tabel_sales
GROUP BY nama

Hasil yang kita peroleh:

+---------+---------+-------+----------+-------+--------+-------+---------+-----------+
| nama    | januari | trx_1 | februari | trx_2 | maret  | trx_3 | jml_trx | total_trx |
+---------+---------+-------+----------+-------+--------+-------+---------+-----------+
| Alfa    |  550000 |     2 |  1015000 |     3 | 375000 |     2 |       7 |   1940000 |
| Bravo   |  310000 |     1 |   525000 |     2 | 450000 |     3 |       6 |   1285000 |
| Charlie |  850000 |     3 |   300000 |     1 | 725000 |     2 |       6 |   1875000 |
| Delta   |       0 |     0 |  1000000 |     2 | 370000 |     1 |       3 |   1370000 |
+---------+---------+-------+----------+-------+--------+-------+---------+-----------+

Pada query diatas, saya sengaja menambahkan baris kosong agar query lebih mudah dibaca.

Pada query diatas kita menggunakan fungsi  COUNT untuk menghitung jumlah baris lebih jauh tentang fungsi ini dapat dibaca pada artikel: Menghitung Field/Kolom Pada Tabel MySQL Dengan Kondisi Tertentu Menggunakan COUNT IF

Perhatikan bahwa pada fungsi COUNT, seperti pada COUNT(IF(MONTH(tgl_trx) = 1, id_trx, NULL)) AS trx_1), kita menggunakan NULL, bukan angka 0 seperti yang kita gunakan pada fungsi SUM

Hal tersebut disebabkan karena fungsi COUNT juga akan menghitung baris dengan nilai 0, sehingga hasil yang kita peroleh menjadi tidak akurat, agar tidak ikut dihitung, kita gunakan nilai NULL

III. Pivot Table Dengan MySQL – Menambahkan Baris Total Dengan WITH ROLLUP

Tabel diatas masih belum lengkap karena belum ada data total nilai transaksi per bulannya, untuk itu mari kita buat baris baru yang berisi total penjualan per bulan, jalankan query berikut:

SELECT  IFNULL(nama, 'TOTAL') AS nama_sales,
	SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,
	SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,
	SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,
	COUNT(id_trx) AS jml_trx,
	SUM( nilai_trx ) AS total_trx
FROM tabel_sales
GROUP BY nama
WITh ROLLUP

Hasil yang kita peroleh adalah:

+------------+---------+----------+---------+---------+-----------+
| nama_sales | januari | februari | maret   | jml_trx | total_trx |
+------------+---------+----------+---------+---------+-----------+
| Alfa       |  550000 |  1015000 |  375000 |       7 |   1940000 |
| Bravo      |  310000 |   525000 |  450000 |       6 |   1285000 |
| Charlie    |  850000 |   300000 |  725000 |       6 |   1875000 |
| Delta      |       0 |  1000000 |  370000 |       3 |   1370000 |
| TOTAL      | 1710000 |  2840000 | 1920000 |      22 |   6470000 |
+------------+---------+----------+---------+---------+-----------+

Pada contoh diatas, terdapat tambahan baris baru yaitu baris TOTAL, sehingga sekarang kita sudah dapat membandingkan total penjualan perbulan.

Pada query diatas, klausa WITH ROLLUP akan menambahkan sebuah baris baru, baris tersebut akan menjumlahkan kolom yang ada fungsi agregatnya, yaitu kolom januari, februari, maret, jml_trx, dan total_trx. Kolom jml_trx terdapat fungsi COUNT, sedangkan yang lain fungsi SUM

Selanjutnya, pada baris baru tersebut, MySQL akan memberikan nilai NULL pada kolom yang ada pada klausa GROUP BY, yang pada contoh diatas, kolom nama_sales.

Agar lebih bermakna, nilai NULL tersebut kita ganti dengan kata TOTAL, untuk itu kita gunakan fungsi IFNULL yaitu pada  IFNULL(nama, 'TOTAL') AS nama_sales, yang artinya jika kolom bernilai NULL, maka ganti dengan kata TOTAL.

Menambahkan Baris Subtotal Pada Pivot Table

Selanjutnya pada kondisi tertentu kita perlu untuk menampilkan data subtotal. Terkait contoh diatas, kita akan membuat subtotal berdasarkan tim, sehingga diketahui kinerja dari masing-masing tim.

Query yang kita perlukan adalah:

SELECT  
	IFNULL( nama, 'SUB TOTAL' ) AS nama_sales,
	IFNULL( tim, 'TOTAL' ) AS sales_tim,
	 
	SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,
	COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,
	SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,
	COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
	SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,
	COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
	
	COUNT(id_trx) AS jml_trx,
	SUM( nilai_trx ) AS total_trx
FROM tabel_sales
GROUP BY tim, nama
WITH ROLLUP

Hasil yang kita peroleh adalah:

+------------+-----------+---------+-------+----------+-------+---------+-------+---------+-----------+
| nama_sales | sales_tim | januari | trx_1 | februari | trx_2 | maret   | trx_3 | jml_trx | total_trx |
+------------+-----------+---------+-------+----------+-------+---------+-------+---------+-----------+
| Alfa       | 1         |  550000 |     2 |  1015000 |     3 |  375000 |     2 |       7 |   1940000 |
| Bravo      | 1         |  310000 |     1 |   525000 |     2 |  450000 |     3 |       6 |   1285000 |
| SUB TOTAL  | 1         |  860000 |     3 |  1540000 |     5 |  825000 |     5 |      13 |   3225000 |
| Charlie    | 2         |  850000 |     3 |   300000 |     1 |  725000 |     2 |       6 |   1875000 |
| Delta      | 2         |       0 |     0 |  1000000 |     2 |  370000 |     1 |       3 |   1370000 |
| SUB TOTAL  | 2         |  850000 |     3 |  1300000 |     3 | 1095000 |     3 |       9 |   3245000 |
| SUB TOTAL  | TOTAL     | 1710000 |     6 |  2840000 |     8 | 1920000 |     8 |      22 |   6470000 |
+------------+-----------+---------+-------+----------+-------+---------+-------+---------+-----------+

Perhatikan bahwa sekarang muncul baris baru dengan nama SUB TOTAL, baris baru ini mucul karena kita menambahkan kolom tim pada klausa GROUP BY, sehingga ketika klausa WITH ROLLUP dijalankan, MySQL akan menghitung subtotal berdasarkan kolom tim + nama.

Mengubah Urutan Data Pivot Table

Jika kita perhatikan, setiap data yang dihasilkan oleh klausa GROUP BY, akan ditampilkan dengan rapi, urut mulai dari nilai yang terkecil ke nilai yang terbesar.

Misal pada contoh sebelumnya, data pada kolom nama_sales diurutkan berdasarkan abjad terkecil ke terbesar, demikian juga dengan kolom tim.

Kenapa demikian? hal ini disebabkan karena ketika menjalankan klausa GROUP BY, MySQL akan sekaligus mengurutkan data secara ascending (urut dari terkecil ke terbesar) yang sering disebut implisit order.

Kita dapat mengubah pola pengurutan tersebut dengan menambahkan ASC atau DESC pada klausa GROUP BY. Misal kita ubah urutan nama dan tim menjadi descending.

Jalankan query berikut:

SELECT  
	IFNULL( nama, 'SUB TOTAL' ) AS nama_sales,
	IFNULL( tim, 'TOTAL' ) AS sales_tim,
	 
	SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,
	COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,
	SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,
	COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
	SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,
	COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
	
	COUNT(id_trx) AS jml_trx,
	SUM( nilai_trx ) AS total_trx
FROM tabel_sales
GROUP BY tim DESC, nama DESC
WITH ROLLUP

Hasil yang kita peroleh:

+------------+-----------+---------+-------+----------+-------+---------+-------+---------+-----------+
| nama_sales | sales_tim | januari | trx_1 | februari | trx_2 | maret   | trx_3 | jml_trx | total_trx |
+------------+-----------+---------+-------+----------+-------+---------+-------+---------+-----------+
| Delta      | 2         |       0 |     0 |  1000000 |     2 |  370000 |     1 |       3 |   1370000 |
| Charlie    | 2         |  850000 |     3 |   300000 |     1 |  725000 |     2 |       6 |   1875000 |
| SUB TOTAL  | 2         |  850000 |     3 |  1300000 |     3 | 1095000 |     3 |       9 |   3245000 |
| Bravo      | 1         |  310000 |     1 |   525000 |     2 |  450000 |     3 |       6 |   1285000 |
| Alfa       | 1         |  550000 |     2 |  1015000 |     3 |  375000 |     2 |       7 |   1940000 |
| SUB TOTAL  | 1         |  860000 |     3 |  1540000 |     5 |  825000 |     5 |      13 |   3225000 |
| SUB TOTAL  | TOTAL     | 1710000 |     6 |  2840000 |     8 | 1920000 |     8 |      22 |   6470000 |
+------------+-----------+---------+-------+----------+-------+---------+-------+---------+-----------+

Pada tabel diatas, data nama_sales dan sales_tim diurutkan secara descending.

Cara ini merupakan cara yang paling mudah untuk mengubah urutan data, namun, pada MySQL versi 5.7, fitur ini sudah deprecated, artinya tidak disarankan untuk digunakan, karena, kedepan fitur ini akan dihapus.

Untuk itu disarankan untuk menggunakan eksplisit order dengan menggunakan klausa ORDER BY. Pada query yang mengandung klausa WITH ROLLUP, penggunaan klausa ORDER BY ini tidaklah mudah.

Hal ini karena kita harus mengatur nama dari kolom hasil klausa WITH ROLLUP sedemikian rupa sehingga berada di urutan paling bawah, perhatikan contoh berikut:

SELECT * FROM
(
	SELECT  
		IFNULL( nama, '1 - SUB TOTAL' ) AS nama_sales,
		IFNULL( tim, '0 - TOTAL' ) AS sales_tim,
		 
		SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,
		COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,
		SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,
		COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
		SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,
		COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
		
		COUNT(id_trx) AS jml_trx,
		SUM( nilai_trx ) AS total_trx
	FROM tabel_sales
	GROUP BY tim,  nama
	WITH ROLLUP
) AS tabel_sales
ORDER BY sales_tim DESC, nama_sales DESC

Hasil yang kita peroleh:

+---------------+-----------+---------+-------+----------+-------+---------+-------+---------+-----------+
| nama_sales    | sales_tim | januari | trx_1 | februari | trx_2 | maret   | trx_3 | jml_trx | total_trx |
+---------------+-----------+---------+-------+----------+-------+---------+-------+---------+-----------+
| Delta         | 2         |       0 |     0 |  1000000 |     2 |  370000 |     1 |       3 |   1370000 |
| Charlie       | 2         |  850000 |     3 |   300000 |     1 |  725000 |     2 |       6 |   1875000 |
| 1 - SUB TOTAL | 2         |  850000 |     3 |  1300000 |     3 | 1095000 |     3 |       9 |   3245000 |
| Bravo         | 1         |  310000 |     1 |   525000 |     2 |  450000 |     3 |       6 |   1285000 |
| Alfa          | 1         |  550000 |     2 |  1015000 |     3 |  375000 |     2 |       7 |   1940000 |
| 1 - SUB TOTAL | 1         |  860000 |     3 |  1540000 |     5 |  825000 |     5 |      13 |   3225000 |
| 1 - SUB TOTAL | 0 - TOTAL | 1710000 |     6 |  2840000 |     8 | 1920000 |     8 |      22 |   6470000 |
+---------------+-----------+---------+-------+----------+-------+---------+-------+---------+-----------+

Pada query diatas, dengan subquery kita buat tabel sementara yang sama persis dengan contoh sebelumnya, baru kemudian hasilnya kita urutkan berdasarkan kolom sales_tim, dan sales_nama.

Perhatikan bahwa pada query diatas, pada SUB TOTAL saya beri awalan angka 1, sehingga jika diurutkan secara descending akan berada di bawah, demikian juga untuk TOTAL pada kolom sales_tim.

Pembahasan tentang membuat total dan subtotal hal ini cukup panjang, sobat dapat mempelajarinya lebih jauh pada artikel: Menghitung Total dan Subtotal Pada MySQL

IV. Pivot Table Dengan MySQL – Menambahkan Baris Total Dengan Query Tersendiri

Pada contoh sebelumnya, kita dapat dengan mudah menambahkan baris total dan subtotal dengan klausa WITH ROLLUP, pada kondisi tertentu, kita tidak dapat mengandalkan klausa tersebut, misal ketika kita ingin menampilkan data berdasarkan kolom nilai_trx yang diurutkan secara descending

Jika kita menggunakan klausa WITH ROLLUP, maka baris TOTAL akan selalu berada diatas, karena pasti jumlahnya paling besar.

Untuk mengatasi hal tersebut, kita dapat menambahkan baris TOTAL dengan query tersendiri, kemudian dengan UNION ALL, hasilnya kita gabung dengan query utama. Adapun querynya adalah:

SELECT * FROM (
	SELECT  
		nama,
		tim,
		 
		SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,
		COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,
		SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,
		COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
		SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,
		COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
		
		COUNT(id_trx) AS jml_trx,
		SUM( nilai_trx ) AS total_trx
	FROM tabel_sales
	GROUP BY tim,  nama
	ORDER BY total_trx DESC
) AS tabel_sales
UNION ALL
SELECT 'TOTAL' AS nama, '' AS tim, 
	SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,
	COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,
	SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,
	COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
	SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,
	COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
	COUNT(id_trx) AS jml_trx,
	SUM( nilai_trx ) AS total_trx
FROM tabel_sales

Hasil yang kita peroleh:

+---------+-----+---------+-------+----------+-------+---------+-------+---------+-----------+
| nama    | tim | januari | trx_1 | februari | trx_2 | maret   | trx_3 | jml_trx | total_trx |
+---------+-----+---------+-------+----------+-------+---------+-------+---------+-----------+
| Alfa    | 1   |  375000 |     2 |   815000 |     3 |       0 |     0 |       5 |   1190000 |
| Delta   | 2   |       0 |     0 |  1000000 |     2 |       0 |     0 |       2 |   1000000 |
| Bravo   | 1   |  310000 |     1 |   525000 |     2 |       0 |     0 |       3 |    835000 |
| Charlie | 2   |  525000 |     2 |   300000 |     1 |       0 |     0 |       3 |    825000 |
| TOTAL   |     | 1535000 |     6 |  2640000 |     8 | 2295000 |     8 |      22 |   6470000 |
+---------+-----+---------+-------+----------+-------+---------+-------+---------+-----------+

Perhatikan bahwa pada query diatas, kita letakkan query pertama pada subquery, hal ini dikarenakan ketika menggunakan UNION ALL, klausa ORDER BY harus berada di paling bawah, sehingga untuk mengurutkan data yang pertama, kita gunakan subquery.

V. Pivot Table Dinamis Dengan MySQL

Pada pembahasan diatas, jumlah kolom hasil pivot table sudah kita tentukan sebelumnya (statis). Hal tersebut pada banyak kondisi sudah cukup memadahi, namun, pada kondisi tertentu, jumlah kolom pada hasil pivot table berubah ubah sesuai dengan jumlah baris.

Pada kondisi tersebut, maka query SQL nya juga ikut berubah ubah, sehingga penggunaan model statis tidak dapat digunakan lagi, untuk itu, kita dapat menggunakan model SQL yang dikenal dengan istilah pivot table dinamis. Pembahasan tentang hal ini dapat diikuti pada tutorial: Pivot Table Dinamis Dengan MySQL

VI. Penutup

Membuat pivot table dengan MySQL cukup mudah dilakukan, kendala utama dari proses ini adalah ketika ingin mengurutkan data yang didalamnya terdapat baris subtotal dan total yang dihasilkan oleh klausa WITH ROLLUP.

Hal ini disebabkan karena baris total dan subtotal tersebut ikut diurutkan, sehingga sangat besar kemungkinan baris tersebut akan berada di baris paling atas, sebagai alternatif, kita dapat membuat baris total menggunakan query tersendiri.

Jika sobat ingin membuat  pivot table menggunakan PHP, sobat dapat mengikuti tutorial: Pivot Table Dengan PHP dan MySQL

Demikian pembahasan mengenai pivot table dengan MySQL, semoga bermanfaat.

Recomended Post

4 Feedback dari pembaca

    • Berarti SQL nya bersifat dinamis ya mas. Karena SQL bukanlah bahasa dinamis (tidak mengenal perulangan) maka untuk membuat SQL dinamis, kita perlu menempuh beberapa langkah. Singkatnnya, query jadiya 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;

      Untuk penjelasan lebih lengkap, dapat dibaca pada tutorial: Pivot Table Dinamis Dengan MySQL

      • Salam buat mas Agus. saya mau tanya kalau punya tabel seperti

        +-------+-----------+-----------+------------+-------+
        | Nama  | Kelurahan | Kecamatan | Bidang     | honor |
        +-------+-----------+-----------+------------+-------+
        | Budi  | Bonai     | Kunta     | Keamanan   | 40000 |
        | Citra | Bonai     | Pulano    | Sekretaris | 50000 |
        | Dodi  | Bonai     | Pulano    | Sekretaris | 50000 |
        | Erni  | Koja      | Sentra    | Bendahar   | 30000 |
        | Ferdi | Koja      | Utaram    | Sekretaris | 40000 |
        | Gina  | Koja      | Sentra    | Staf       | 30000 |
        | Heru  | Koja      | Sentra    | Staf       | 30000 |
        | Irina | Koja      | Sentra    | Bendahara  | 30000 |
        | Jude  | Koja      | Utaram    | Sekretaris | 40000 |
        | Kindi | Koja      | Sentra    | Staf       | 30000 |
        | Linda | Koja      | Sentra    | Staf       | 30000 |
        +-------+-----------+-----------+------------+-------+

        Kalau pivot tabel MySQL yang hasilnya per Kecamatan seperti ini gimana?

        Misalnya Kecamatan Bonai

        +----------+-----+--------+----------+---------+----+-----+
        |Kelurahan |Sain |Keamanan|Sekretaris|Bendahara|Staf|Total|
        +----------+-----+--------+----------+---------+----+-----+
        |Kunta     |    1|       1|         0|        0|   0|    2|
        |Pulano    |    0|       0|         2|        0|   0|    2|
        |Total     |    1|       1|         2|        0|   0|    4|
        +----------+-----+--------+----------+---------+----+-----+

        Di Kecamatan Koja menjadi
        Misalnya Kecamatan Koja

        +----------+-----+--------+----------+---------+----+-----+
        |Kelurahan |Sain |Keamanan|Sekretaris|Bendahara|Staf|Total|
        +----------+-----+--------+----------+---------+----+-----+
        |Sentra    |    0|       0|         0|        2|   4|    6|
        |Utaram    |    0|       0|         2|        0|   0|    2|
        |Total     |    0|       0|         2|        2|   4|    8|
        +----------+-----+--------+----------+---------+----+-----+

        • Coba pakai query ini mas:

          SELECT  kelurahan,
          	COUNT(IF (bidang = 'sain', 1, NULL)) AS Sain,
          	COUNT(IF (bidang = 'keamanan', 1, NULL)) AS Keamanan,
          	COUNT(IF (bidang = 'sekretaris', 1, NULL)) AS Sekretaris,
          	COUNT(IF (bidang = 'bendahara', 1, NULL)) AS Bendahara,
          	COUNT(IF (bidang = 'staf', 1, NULL)) AS Staf,
          	
          	COUNT(IF (bidang = 'sain', 1, NULL))	
          	+ COUNT(IF (bidang = 'keamanan', 1, NULL))
          	+ COUNT(IF (bidang = 'sekretaris', 1, NULL))
          	+ COUNT(IF (bidang = 'bendahara', 1, NULL))
          	+ COUNT(IF (bidang = 'staf', 1, NULL))
          	AS total
          FROM kec
          WHERE kecamatan = 'Sentra'
          GROUP BY kecamatan;

          Hasil:

          +-----------+------+----------+------------+-----------+------+-------+
          | kelurahan | Sain | Keamanan | Sekretaris | Bendahara | Staf | total |
          +-----------+------+----------+------------+-----------+------+-------+
          | Koja      |    0 |        0 |          0 |         1 |    4 |     5 |
          +-----------+------+----------+------------+-----------+------+-------+

          Jika data bidang sifatnya dinamis, bisa digunakan teknik pivot table dinamis mas: Pivot Table Dinamis Dengan MySQL

Silakan tinggalkan komentar

Like Us

Dapatkan update artikel terbaru via E-Mail