ffCCBot/2.0 (http://commoncrawl.org/faq/)
NEW!! Buku Query MySQL Lihat Detail » x

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

12 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

  • salam mas agus..
    saya punya problem yg sama seperti ini modelnya mempivot table di MySql, saya sudah mencoba buat querynya namun tidak berhasil.

    saya mau mempivot dari view msv_inspeksi_x dan table image_temuan_inspeksi.

    untuk view msv_inspeksi_x seperti ini tampilannya :

    +----------+---------------+---------------+---------------+------+------------------+---------+-------------------------------------------+---------------------+-------+-------+---------------------+
    |unit_usaha|  id_inspeksi  | vc_no         | temuan        |resiko|jenis_temuan	     |vc_status|rekomendasi                                |kategori_pelanggaran |pic    |nama	 |due_date |tgl_selesai|
    +----------+---------------+---------------+---------------+------+------------------+---------+-------------------------------------------+---------------------+-------+-------+---------+-----------+
    |JB	   |IP201611000001 |IP2016110000011| Jalan Rusak   |HIGH  |KONDISI TIDAK AMAN|CLOSE    |Jalan harus diperbaiki dan di aspal ulang  |SOP	                 |1401345|AMELIA |2017-11-1|2017-4-1   |
    +----------+---------------+---------------+---------------+------+------------------+---------+-------------------------------------------+---------------------+-------+-------+---------------------+

    tampilannya disini berantakan mas jd ngga jelas gmn wujud tampilannya, karena panjang sekali datanya jd ngga muat dikolom komentar ini, mungkin bisa di copy paste ke notedpad tanpa format word wrap sekiranya bisa dilihat wujud tampilan viewnya hehehe..

    dan table image_inspeksi_temuan seperti ini;

    +---------------+--------------------------+-----------+------------+
    |unit_usaha     | no_id                    |   ket     |  gambar    |
    +---------------+--------------------------+-----------+------------+
    |JB	            |IP2016110000011|before    | (BLOB)    |
    +---------------+--------------------------+-----------+------------+
    |JB	            |IP2016110000011|after     | (BLOB)    |
    +---------------+--------------------------+-----------+------------+

    nah saya mau hasil query nya menjadi seperti ini ;

    +----------+---------------+---------------+---------------+------+------------------+---------+-------------------------------------------+---------------------+-------+-------+---------------------+------+------+
    |unit_usaha|  id_inspeksi  | vc_no         | temuan        |resiko|jenis_temuan	     |vc_status|rekomendasi                                |kategori_pelanggaran |pic    |nama	 |due_date |tgl_selesai|before| after|
    +----------+---------------+---------------+---------------+------+------------------+---------+-------------------------------------------+---------------------+-------+-------+---------+-----------+------+------+
    |JB	   |IP201611000001 |IP2016110000011    | Jalan Rusak   |HIGH  |KONDISI TIDAK AMAN|CLOSE    |Jalan harus diperbaiki dan di aspal ulang  |SOP	                 |1401345|AMELIA |2017-11-1|2017-4-1   |(BLOB)|(BLOB)|
    +----------+---------------+---------------+---------------+------+------------------+---------+-------------------------------------------+---------------------+-------+-------+---------------------+------+------+

    berantakan lagi tampilannya 😀 saya tidak bisa merapikannya dikolom komentar ini, mohon bantuannya mas..
    semoga berkenan.. terimakasih..

    • Untuk code panjang bisa menggunakan tag <pre> mbak. Untuk kasus diatas, coba gunakan JOIN mbak:

      SELECT mi.*, before, after
      FROM msv_inspeksi_x AS mi
      LEFT JOIN image_inspeksi_temuan ON mi.no_vc = image_inspeksi_temuan.no_id

  • tidak bisa di jalankan mas query nya, nama kolom before dan after di baca fungsi mas jadi saya ganti isi valuenya menjadi sebelum dan sesudah, namun ketika di jalankan dengan query seperti ini:

    SELECT mi.*, sebelum, sesudah
    FROM msv_inspeksi_x AS mi
    LEFT JOIN image_inspeksi_temuan ON mi.no_vc = image_inspeksi_temuan.no_id
    

    muncul error seperti ini mas:
    Unknown column ‘sebelum’ in ‘field list’

    kolom sebelum tidak di kenali mas..

    • Coba tetap menggunakan before dan after tetapi menggunakan backtick mbak:

      SELECT mi.*, `before`, `after`
      FROM msv_inspeksi_x AS mi
      LEFT JOIN image_inspeksi_temuan ON mi.no_vc = image_inspeksi_temuan.no_id

      Error: Unknown column ‘sebelum’ in ‘field list’ berarti kolom sebelum tidak ditemukan, bisa jadi kolom tersebut di tabel image_inspeksi_temuan tidak ada

  • mau nanya mas, saya punya contoh data seperti ini

    id_trx	nama	tgl_trx	       tim	nilai_trx
    1	Bravo	11/03/2016	1	425000
    2	Bravo	18/03/2016	1	250000
    3	Charlie	23/01/2016	2	350000
    4	Charlie	22/02/2016	2	300000
    5	Charlie	21/03/2016	2	275000
    

    pengen jdi seperti ini gan:

    id_trx	nama	tgl_trx	       tim	nilai_trx
    1	Bravo	11/03/2016	1	425000
    		18/03/2016	1	250000
    TOTAL				        675000
    3	Charlie	23/01/2016	2	350000
    		22/02/2016	2	300000
    		21/03/2016	2	275000
    TOTAL		                        925000
    

    • Untuk model seperti itu, setahu saya perlu menggunakan bahasa pemrograman struktural mas, seperti php, karena untuk memunculkan total, tidak bisa menggunakan WITH ROLLUP, karena tidak ada data yang di group

  • Permisi mas agus saya punya masalah pada saat run prepare stmt from @sql
    mohon dicek sql saya:

    SELECT * FROM tabel_nilai;
    SET @sql=NULL;
    ##2
    (SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(case when`nama`=''',tabel_nilai.nama,'''then nilai end) AS',tabel_nilai.nama) ORDER BY tabel_nilai.id ASC SEPARATOR',\n') INTO @sql FROM tabel_nilai);
    #3
    SET @sql=CONCAT('SELECT tabel_nilai.order, ', @sql, 'from tabel_nilai GROUP BY tabel_nilai.order');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    

Silakan tinggalkan komentar

Like Us

Dapatkan update artikel terbaru via E-Mail
  1. Eksklusif Cheat Sheet PHP Bahasa Indonesia – Pendamping Belajar PHP

  2. PHP FORM III: Menampilkan Data MySQL Dengan PHP dan Form HTML

  3. Tutorial PHP Form II: Menampilkan Hasil Input Form HTML dengan PHP

  4. Tutorial PHP Form I: Menghubungkan Form HTML dengan PHP

  5. Menampilkan Data Dari Beberapa Tabel MySQL – JOIN Pada MYSQL

  6. Memahami dan Menampilkan Tanggal dan Waktu Pada PHP

  7. Menghitung Selisih Waktu Dengan PHP – Cara Termudah

  8. Memahami Fungsi Date Pada PHP

  9. Memahami Fungsi Time, Strtotime, dan Mktime Pada PHP – Memanipulasi Waktu

  10. Memahami Zona Waktu (Timezone) dan Selisih Waktu Pada PHP