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.

Subscibe Now

Suka dengan artikel di Jagowebdev.com? jangan sampai ketinggalan, segera join ke milis kami untuk update informasi terbaru dari Jagowebdev.com

Komitmen Kami: Kami senantiasa menghargai privasi Anda dan tidak akan membagikan identitas Anda ke pihak manapun.

84 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

  • Permisi pak, saya menggunakan xampp (phpmyadmin) .. apakah bisa menggunakan query yg ada di atas untuk pivot tablet dinamis? Terima kasih.

  • Permisi mas, mau nanya
    Kalau mau menjalankannya di php bagaimana caranya?
    Saya baca beberapa ada yang bilang menggunakan fungsi CALL

  • mas agus saya sudah berhasil membuat privot static saat saya convert ke privot dinamis kok error ya mas

    SELECT  dept_id, 
    	COUNT(IF(userpemakai_id = 'EM001', dept_id, NULL)) AS a, 
    	COUNT(IF(userpemakai_id = 'EM002', dept_id, NULL)) AS b, 
    	COUNT(IF(userpemakai_id = 'EM003', dept_id, NULL)) AS c
    FROM tb_activity
    GROUP BY dept_id
    

    query berhasil

    kode dinamisnya

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

    tidak berhasil ada error
    ini pesan error di php myadmin
    Fatal error: Call to a member function getClauses() on null in C:\wamp\apps\phpmyadmin4.6.4\libraries\sql-parser\src\Utils\Query.php on line 549

  • salam, maaf mau tanya pa.. saya menampilkan yang seperti contoh ini, script query yang saya buat begini:

    SET @sql = NULL;
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(IF(pa.NILAI_KE = ''',
          NILAI_KE,
          ''', pa.NILAI, NULL)) AS ',
          NILAI_KE
        )
      ) INTO @sql
    FROM t_simpel_nilai_mahasiswa;
    
    SET @sql = CONCAT('SELECT p.NOMOR_INDUK_MAHASISWA
                        , p.NM_MAHASISWA
                        , p.JNS_KELAMIN ', @sql, ' 
                       FROM t_simpel_mahasiswa p
                       LEFT JOIN t_simpel_nilai_mahasiswa AS pa 
                        ON p.NOMOR_INDUK = pa.NOMOR_INDUK_MAHASISWA
                       GROUP BY p.NOMOR_INDUK');
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    

    ketika di eksekusi di php myadmin hasilnya error begini:
    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘MAX(IF(pa.NILAI_KE = ‘1’, pa.NILAI, NULL)) AS 1,MAX(IF(pa.NILAI_KE = ‘2’, pa.NIL’ at line 3

    kenapa ya kira2?

  • Manteps banget mas agus jarang-jarang ada penyedia artikel mengulas secara tuntas dan jelas seperti diatas, dan sudah 2 kali saya dibantu oleh artikel2 mas agus. Oh ya mas saya punya kode mysql seperti ini:

    SELECT DISTINCT a.id, SUM(IF( YEAR(a.tgl_setor) LIKE "%2017%", a.total_bayar, 0)) AS 'totalbayar_2017'
    , IF(b.tahun LIKE "%2017%", b.nilai_target_rpkd, 0) AS 'target_2017'
    , SUM(IF( YEAR(a.tgl_setor) LIKE "%2018%", a.total_bayar, 0)) AS 'totalbayar_2018'
    , IF(b.tahun LIKE "%2018%", b.nilai_target_rpkd, 0) AS 'target_2018' 
    FROM tbl_transaksi_bandara a 
    JOIN tbl_target_rpkd b ON b.jenis_rpkd_id=a.jenis_rpkd_id 
    JOIN tbl_jenis_rpkd c ON c.id=a.jenis_rpkd_id 
    WHERE a.status_billing='1' 
    GROUP BY b.id  
    
    Dari kode mysql diatas harapan saya menghasilkan tabel seperti ini:
    
    |totalbayar_2017|target_2017|totalbayar_2018|target_2018|
    |200000         |300000     |350000         |400000     |
    
    Namun ketika saya eksekusi hasilnya malah seperti ini mas:
    
    |totalbayar_2017|target_2017|totalbayar_2018|target_2018|
    |200000         |300000     |0              |0          |
    |200000         |0          |350000         |400000     |
    

    Mohon bantuannya ya mas agus, Terimakasih sebelumnya.

      • Terimakasih telah direspon mas agus, Saya tampilin b.id dan a.id nya itu b.id nya beda antara baris ke baris lainnya. Ada solusi lain nggak mas kalau misalnya mau nampilin seperti ini:

        |id_jnsrpkd|jns_rpkd|totalbayar_2017|target_2017|totalbayar_2018|target_2018|
        |1|Pemakaian Kios|350000|600000|400000|700000|
        |2|Pemakaian Lahan|400000|500000|0|600000|
        |3|Pemakaian Produk|300000|600000|300000|700000|
        
        Dengan tabel database:
        Tabel Transaksi:
        |id|jns_rpkd_id|tgl_bayar|total_bayar|
        |1|2|13-08-2017|400000     |
        |2|3|13-08-2017|300000     |
        |3|1|13-08-2017|350000     |
        |4|1|13-08-2018|400000     |
        |5|3|13-08-2018|300000     |
        

        Tabel Target:

        |id|jns_rpkd_id|tahun_target|target|
        |1|2|2017|500000|
        |2|3|2017|600000|
        |3|1|2017|600000|
        |4|2|2018|600000|
        |5|3|2018|700000|
        |6|1|2018|700000|
        

        Tabel Jns_Rpkd:

        |id|jns_rpkd|
        |1|Pemakaian Kios|
        |2|Pemakaian Lahan|
        |3|Pemakaian Produk|
        

        Gitu mas mohon dibantu mas ya, Terimakasih sebelumnya mas. Semoga pengunjung yang lain yang mengalamin masalah yang sama dengan saya bisa terbantukan juga dengan analisis masalah yang saya alami ini.

        • Untuk menyelesaikan permasalahan tersebut, gabugkan ketiga tabel mas…
          Ingat karakteristik join, dia akan menampilkan semua kombinasi yang mungkin, sehingga jika salah atu tabel berisi lebih dari satu baris, dengan jns_rpkd_id yang sama, maka pasti baris yang dihasilkan lebih dari satu… maka kita harus menggabungkan menjadi satu menggunakan GROUP BY dengan kolom yang unik

          Misal:

          SELECT id, jns_rpkd
          	, SUM(IF(tahun_target = 2017, tahun_target, NULL)) AS "Target 2017"
          FROM Jns_Rpkd AS jr
          LEFT JOIN target ON target.jns_rpkd_id = jr.id 
          GROUP BY jr.id

          Selanjutnya:

          SELECT id, jns_rpkd
          	, SUM(IF(tahun_target = 2017, tahun_target, NULL)) AS "Target 2017"
          	, SUM(IF(tgl_bayar LIKE "%2017", total_bayar, NULL)) AS "Bayar 2017"
          FROM Jns_Rpkd AS jr
          LEFT JOIN target ON target.jns_rpkd_id = jr.id 
          LEFT JOIN transaksi transaksi.jns_rpkd_id = jr.id
          GROUP BY jr.id
  • sebelumnya terimakasih utk query nya, tapi bagaimana jika saya ingin menampilkan jumlah dari kolom CPV_COST dengan DISTINCT pada kolom id_payment_type ?
    query sy :

    SELECT SUM(DISTINCT(IF(id_payment_type='1', cpv_cost, NULL))) AS Total FROM tbl_payment;
    tapi hasilnya malah 2500 bukanya 5000
    +----+-----------------+------------+----------+
    | ID | id_payment_type | cpv_number | cpv_cost |
    +----+-----------------+------------+----------+
    |  1 |               1 | 2210       | 2500     |
    |  2 |               1 | 2210       | 2500     |
    |  3 |               2 | 2211       | 1000     |
    |  4 |               1 | 2012       | 2500     |
    +----+-----------------+------------+----------+
    

    Mohon pencerahan querynya mas,,, thx

  • terima kasih pak. sangat membantu.
    bagaimana jika ingin menampilkan data dicrosstab itu berupa huruf, contoh :

    Nama|Biologi|Fisika|Matematika
    Andi|A|C|D
    Budi|C|B|A
    Cici|D|A|C
  • Gan ane udah berhasil ngemodifikasi syntaxnya sesuai dengan yg ane butuhin. Nah sekarang ane bingung bagaimana cara manggil syntax tersebut di php ya, thx gan

  • Mas, kalo misal eksekusinya bukan di jumlah (SUM).. Apakh bisa kita gunakan function yg di dalamnya mengandung rumus??

  • 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 klausa order
    );

    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;

    disitu mas agus naruh sql ordernya?? sy coba blm bisa
    makasih mas bimbingannya

    • Di bagian ini mas:

      SET @sql_dynamic = (
      	SELECT
      		GROUP_CONCAT( DISTINCT
      			CONCAT(
      				'SUM( IF(MONTH(trx_date) = '
      				, MONTH(trx_date)
      				, ', amount,0) ) AS mo_'
      				, MONTH(trx_date)
      			)
      		)
      	FROM sales_table
      	ORDER BY trx_date DESC
      );
  • Dear Mas Agus,

    Terima Kasih, Artikelnya sangat membantu 🙂

    Apakah query tsb bisa diimplementasikan di php mas ? jika bisa bagaimana cara membuat kolom dinamis pada datatable php ?

    Terima Kasih,

    Salam

  • mas kalo MONTH itu diganti dengan nama tabelnya bisa ga ? soalnya kasus saya tidak perbulan tampil datanya, tetapi per nama Program, mohon bantuannya terima kasih

  • Mas saya mau tanya tentang error dalam query ini :

    BEGIN
    SET @sql_dinamis = (
    		SELECT
    			GROUP_CONCAT( DISTINCT
    				CONCAT('SUM( IF(nm_divisi = '
    					, nm_divisi
    					, ', ren_biaya, 0) ) AS '
    					, nm_divisi
    				)
    			)
    		FROM tbl_beban
    	);
    
    SET @SQL = CONCAT('SELECT IFNULL(aa.nm_akun, "TOTAL") AS NAMA_AKUN, ', 
    			  @sql_dinamis, ', \r\n\t\t\t  SUM(ren_biaya) AS JUMLAH\r\n\t\t   FROM tbl_beban as a,akun as aa\r\n           WHERE a.kodrek_akun=aa.kodrek_akun\r\n\t\t   GROUP BY aa.nm_akun\r\n\t\t   WITH ROLLUP'
    	);
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    END
    

    notif dr mysql : #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘PENGENDALIAN SISTEM OPERASI, ren_biaya, 0) ) AS VP PENGENDALIAN SISTEM OPERASI, ‘ at line 1

    itu kira2 salah dimana nya ya mas ?

  • Mas maaf saya mau tanya pke parameter, knapa pas disearch data yg NULL nya tetep muncul ya mas ? adakah cara agar data yg NULL tidak terbawa tampil juga mas ? terima kasih sebelumnya

    CALL rekap_divisi(‘VP PENGENDALIAN SISTEM OPERASI’);
    +—-+———————+————+——————+
    | ID | NAMA AKUN | PROKER1 | JUMLAH |
    +—-+———————+————+——————+
    | 1 | Pengendalian | NULL | 2500 |
    | 2 | Pemanfaatan | NULL | 2500 |
    | 3 | Pengeboran | 2211 | 2211 |
    | 4 | Perbaikan | 2500 | 2500 |
    +—-+———————+————–+—————-+

  • permisi gan, misalnya saya sdh buat pivot dengan hasil seperti ini, kemudian saya mau menampilkan dengan pertanggal bayar
    itu gmana ya caranya..?
    ————————————————————————————–
    tgl_bayar | kelompok_1 | kelompok_2 | kelompok_3 |
    _____________________________________________________________
    2018-09-20 | 5000 | | |
    2018-09-21 | | 7000 | |
    2018-09-22 | | | 5000 |

  • +------------+---------+-------+---------+-------+---------+-------+---------+---------+
    | 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 |
    +------------+---------+-------+---------+-------+---------+-------+---------+---------+

    mas saya mau tanya dari contoh diatas, kalau sebelum kolom sales dikasih kolom kode sales, nah itu nti cara penambahan TOTAL nya gima mas ? trima kasih sebelumnya

  • +------------+------------+---------+-------+---------+-------+---------+-------+---------+---------+
    | kode_sales | nama_sales | bln_1   | trx_1 | bln_2   | trx_2 | bln_3   | trx_3 | JML_TRX | TOTAL   |
    +------------+------------+---------+-------+---------+-------+---------+-------+---------+---------+
    | 0001       | Alfa       |  375000 |     2 |  815000 |     3 |  375000 |     2 |       7 | 1565000 |
    | 0002       |  310000    |       1 |  525000 |     2 |  825000 |     3 |       6 | 1660000 |
    | 0003       |  850000    |       3 |  300000 |     1 |  725000 |     2 |       6 | 1875000 |
    | 0004       |       0    |       0 | 1000000 |     2 |  370000 |     1 |       3 | 1370000 |
    | TOTAL      | NULL       | 1535000 |     6 | 2640000 |     8 | 2295000 |     8 |      22 | 6470000 |
    +------------+------------+---------+-------+---------+-------+---------+-------+---------+---------+
    

    pengennya saya kaya gini mas totalnya, gimana cara buat NULL pada kolom keduanya mas ? trima kasih

  • Pagi Mas maaf sebelumnya,

    untuk syntax diatas menggunakan mysql jika ke Postgresql bagaimana ya karena ketentuan di postgre sangat berbeda sekali ya… terima kasih..

  • Pagi Mas Agus,
    Saya sempat membaca dan mempelajari, tulisan Mas Agus di jagowebdev.com, mengenai pivot table dengan php dan mysql.
    Agak bingung,karena saya newbie… 😀

    Boleh ijin konsultasi Mas Agus, jika saya punya table sbb :

    (1) Tabel Kode Warna

    +———+—————–+
    | kode | penjelasan |
    +———+—————–+
    | MR | Merah |
    | KN | Kuning |
    | HJ | Hijau |
    | BR | Biru |
    +———+—————–+

    (2) Tabel Penggunaan Cat

    +————–+———+——————–+
    | tanggal | kode | penggunaan |
    +————–+———+——————–+
    | 01/01/18 | MR | 2 |
    | 01/01/18 | KN | 1 |
    | 01/01/18 | BR | 4 |
    | 02/01/18 | BR | 1 |
    | 01/01/18 | MR | 5 |
    +————–+———+——————–+

    (3) Tampilan di Web yang diharapkan

    * asc
    +———+——+—–+—–+——+
    | X | BR | HJ | KN | MR |
    +———+——+—–+—–+——+
    | Total | 5 | 0 | 1 | 7 |
    +———+——+—–+—–+——+

    * desc
    +———+——+—–+—–+——+
    | X | MR | KJ | HJ | BR |
    +———+——+—–+—–+——+
    | Total | 7 | 1 | 0 | 5 |
    +———+——+—–+—–+——+

    Summary total penggunaan cat (seperti transpose di excel, dengan urutan ASC atau DESC)

    Apakah bisa dibantu untuk logika di script SQL-nya dan script PHP-nya, agar bisa tampil di brower seperti point-3 ?

    Terima Kasih,

    • Kriteria ASC dan DESC nya berdasarkan kolom apa mas?
      Misal ASC berdasarkan tanggal:

      $sql = 'SELECT SUM(IF(kode="MR", penggunaan, NULL)) AS MR
      		, SUM(IF(kode="KN", penggunaan, NULL)) AS KN
      		, SUM(IF(kode="BR", penggunaan, NULL)) AS BR
      	FROM tabel 
      	ORDER BY tanggal ASC';
      
      $query = mysqli_query($conn, $sql);
      while ($row = mysqli_fetch_array($query)) {
      	...
      }
  • 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;

    Muncul ERROR :
    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ” at line 14

    Kira2 Apakah ada yang salah kode ya pak? Mohon pencerahannya

  • Selamat sore Mas Agus, terima kasih artikelnya sangat membantu
    saya mencoba mengaplikasikan di tabel saya dengan query seperti ini:
    SET @sql_dinamis = (
    select
    GROUP_CONCAT( DISTINCT
    CONCAT (‘SUM (IF(NAMA_MODEL = ‘
    ,NAMA_MODEL
    ,’,JUMLAH,0)) AS ‘
    , NAMA_MODEL
    )
    )
    from v_barang3
    );

    SET @SQL = CONCAT(‘SELECT nama_lokasi, ‘,
    @sql_dinamis, ‘
    FROM v_barang3 where nama_lokasi is not null
    GROUP BY nama_lokasi WITH ROLLUP’
    );

    PREPARE TEST FROM @SQL;
    EXECUTE TEST;
    DEALLOCATE PREPARE TEST;

    tapi setiap di run muncul message di bawah ini:
    You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘FROM v_barang3 where nama_lokasi is not null
    GROUP BY nama_lokasi WITH ROL’ at line 2

    mananya yang kurang tepat ya mas?
    terima kasih, saya tunggu jawabannya.

    • Bisa di cek dulu isi variabel @sql nya seperti apa mas, bisa menggunakan perintah SELECT @sql (seperti di artikel) selanjutnya hasilnya di copy paste di jalankan manual, cek error nya dimana

  • selamat pagi pak agus, mau tanya lagi. saya menggunakan store procedure di workbench dengan aql sbb :

    CREATE DEFINER=`u819696343_pos2`@`%` PROCEDURE `rekapjual`()
    BEGIN
    SET @sql_dinamis = (
    SELECT
    GROUP_CONCAT( DISTINCT
    CONCAT(‘IF(vwpwnjualan.idpel = ‘
    , vwpwnjualan.idpel
    , ‘,1,0)AS ‘
    , vwpwnjualan.idpel
    )
    )
    FROM vwpwnjualan
    );

    SET @SQL = CONCAT(‘SELECT vwpwnjualan.kode, ‘,
    @sql_dinamis, ‘
    FROM vwpwnjualan
    GROUP BY vwpwnjualan.kode’
    );

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

    saya ingin keluar seperti ini saja

    kode AGC
    C 1
    B 2
    A 3

    Saya call rekapjual muncul :
    Error Code: 1054. Unknown column ‘AGC’ in ‘field list’

    mohon pencerahannya pak agus
    terima kasih

  • Halo gan Agus, terima kasih banyak atas penjelasannya. Sangat jelas menurut saya cara penyampaiannya. Btw nulis artikel inggris juga ya di webdevzoom? Soalnya sama percis dengan yang di sana haha.

  • MANTAF TUTORNYA DAN SANGAN MEMBANTU…TAPI YANG SAYA MASIH BINGUN KLU PIVOT TABLEL DINAMIS DI IMPLEMENTASIKAN DENGAN PHP MAS…MOHON PENCERAHANNYA

  • KD_PROPINSI|KD_DATI2|KD_KEC|KD_KEL|KD_BLOK|NO_URUT|KD_JNS_OP|TAHUN|TAGIHAN
          69      72          010	  006	  015	  0113	  0	      2019	  100000
          69      72	  012	  002	  018	  0009	  0	      2019	  10000
          69      72	  010	  006	  015	  0113	  0	      2018	  10000
          69      72	  012	  002	  018	  0009	  0	      2018	  20000
          69      72	  010	  006	  015	  0113	  0	      2017	   0
          69      72	  012	  002	  018	  0009	  0	      20017        0
    

    MAS MISAL SAYA PUNYA DATA SEPERTI DIATAS , TRUS SAYA MAU PIVOT MENJADI SEPERTI INI

    KD_PROPINSI|KD_DATI2|KD_KEC|KD_KEL|KD_BLOK|NO_URUT|KD_JNS_OP|2017||2018||2019
               69	                72	    010	        006	          015	  0113	       0             0      10000   100000
               69	                72	    012	        002	          018	  0009	       0	      0       20000   10000
    

    SAYA COBA PAKE PIVOT DINAMIS TAPI MASIH ERROR

    BEGIN
        
        SET @sql = NULL;
    SELECT
    GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(`TAHUN` = ''',`TAHUN`,''',TAGIHAN,NULL)) AS ',CONCAT('`',`TAHUN`,'`'))
    ) INTO @sql
    
    FROM SPPT ;
    SET @sql = CONCAT('SELECT KD_PROPINSI,KD_DATI2,KD_KECAMATAN,KD_KELURAHAN,KD_BLOK,NO_URUT,KD_JNS_OP,', @sql,'FROM SPPT ' );
    
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET @sql = NULL;
    END
    
  • sdh sy coba lg nggak error mas…cuma errornya lg klu datanya banyak..kan saya punya data 3jt..

  • #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FROM SPPT WHERE THN_PAJAK_SPPT BETWEEN “2005” AND “2019” GROUP by KD_PROPINSI,’ at line 1
    tapi ketika saya ambil data dari 2005 sampe 2015 tidak error mas

    ini sc nya mas:

    BEGIN
    SET @THN_PAJAK_SPPT_1 = THN_PAJAK_SPPT_1;
    SET @THN_PAJAK_SPPT_2 = THN_PAJAK_SPPT_2;

    SET @sql = NULL;
    SELECT
    GROUP_CONCAT(DISTINCT CONCAT(‘MAX(IF(`THN_PAJAK_SPPT` = ”’,`THN_PAJAK_SPPT`,”’,PBB_YG_HRS_DIBAYAR_SPPT,NULL)) AS ‘,CONCAT(‘`’,`THN_PAJAK_SPPT`,’`’))
    ) INTO @sql

    FROM SPPT WHERE THN_PAJAK_SPPT BETWEEN @THN_PAJAK_SPPT_1 AND @THN_PAJAK_SPPT_2;
    SET @sql = CONCAT(‘SELECT KD_PROPINSI,KD_DATI2,KD_KECAMATAN,KD_KELURAHAN,KD_BLOK,NO_URUT,KD_JNS_OP,’, @sql,’ FROM SPPT WHERE THN_PAJAK_SPPT BETWEEN “‘,@THN_PAJAK_SPPT_1,'” AND “‘,@THN_PAJAK_SPPT_2,'”
    GROUP by KD_PROPINSI,KD_DATI2,KD_KECAMATAN,KD_KELURAHAN,KD_BLOK,NO_URUT,KD_JNS_OP’);

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET @sql = NULL;
    END

  • BEGIN
    SET @sql_dinamis = (
    SELECT
    GROUP_CONCAT( DISTINCT CONCAT(‘SUM( IF(THN_PAJAK_SPPT = ‘,THN_PAJAK_SPPT,’,PBB_YG_HRS_DIBAYAR_SPPT, 0) ) AS ‘,THN_PAJAK_SPPT ))
    FROM SPPT
    );
    SET @SQL = CONCAT(‘SELECT KD_PROPINSI,KD_DATI2,KD_KECAMATAN,KD_KELURAHAN,KD_BLOK,NO_URUT,KD_JNS_OP,’,@sql_dinamis,’ FROM SPPT
    GROUP by KD_PROPINSI,KD_DATI2,KD_KECAMATAN,KD_KELURAHAN,KD_BLOK,NO_URUT,KD_JNS_OP’);

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

    MAS SAYA COBA PAKE QUERY INI TETAPI KOK DATA YANG KELUAR HANYA DARI TAHUN 1994 S/D 2006 SAJA, PADAHAL DIDATABASE FIELD THN_PAJAK_SPPT DARI 1994 S/D 2019…MOHON PENCERAHANNYA…TRIMA KASIH BANYAK MAS

  • SET @sql_dinamis = (
    SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(‘SUM(IF( KODE_GUDANG=’,
    KODE_GUDANG, ‘, QTY, 0)) AS ‘
    , replace(KODE_GUDANG, ‘ ‘, ”)
    )
    )
    FROM
    saldo_stok
    );

    SET @sql = CONCAT(‘SELECT KODE_PRODUK, ‘,@sql_dinamis,’
    FROM saldo_stok
    GROUP BY KODE_PRODUK’);

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

    saya menjalankan query diatas tapi mendapat error seperti ini
    #1054 – Unknown column ‘GD000006’ in ‘field list’

    kemudian saya ganti baris ini PREPARE stmt FROM @sql; menjadi PREPARE stmt FROM @sql_dinamis; saya mendapat error sprti ini :
    You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘SUM(IF( KODE_GUDANG=GD000006, QTY, 0)) AS GD000006,SUM(IF( KODE_GUDANG=GD000002,’ at line 1

  • Kalau disimpan menjadi tabel view apakah bisa?
    saya coba muncul error 1064

    SET @sql_dinamis = ( SELECT GROUP_CONCAT( DISTINCT CONCAT( ‘COUNT( IF(MONTH(tanggal_kirim) = ‘, MONTH ( tanggal_kirim ), ‘,dari,0) ) AS ‘, DATE_FORMAT( tanggal_kirim, “%b_%y” ) ) ) FROM view_spp );

    SET @SQL = CONCAT( ‘SELECT dari, ‘, @sql_dinamis, ‘
    FROM view_spp
    GROUP BY dari WITH ROLLUP’ );
    PREPARE stmt
    FROM
    @SQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

  • Mantap.
    Terima kasih atas tutorialnya mas… jelas sekali. Saya sudah cari-cari untuk tabel punya saya sendiri, baru dari artikel ini saya bisa mengimplementasikan pivot untuk tabel saya.
    Teruskan menulisnya, mas.

  • Mas Agus..
    Mantap mas. sukses…

    Boleh nanya mas?
    Bagaimana implementasi Pivot dengan Query Dinamis di OOP?
    Mohon bisa dishare tutorialnya. untuk model tabel sederhana saja (header satu baris)

    Nama, trx_january, trx_februari, trx_maret, Total Trx.

    Terimakasih mas.

  • kalau ini pake inner join bener gk mas, mash blm work soalnya

    SET @sql_dinamis = (
    SELECT
    GROUP_CONCAT( DISTINCT
    CONCAT(‘SUM( IF(pomitra.idpodetail = ‘
    , pomitra.idpodetail
    , ‘,pomitra.jumlah,0) ) AS variant_’
    , podetail.variant
    )
    )
    FROM pomitra inner join podetail on pomitra.idpodetail=podetail.idpodetail
    );

    SET @SQL = CONCAT(‘SELECT admin_mitra.namamitra, ‘,
    @sql_dinamis, ‘
    FROM pomitra inner join admin_mitra on pomitra.idmitra=admin_mitra.idadmin
    GROUP BY pomitra.idmitra WITH ROLLUP’
    );

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

    • Bisa coba di running satu per satu querynya mas, kemudian dicari dimana kesalahannya, misal dijalankan dulu query:
      SELECT
      GROUP_CONCAT( DISTINCT
      CONCAT(‘SUM( IF(pomitra.idpodetail = ‘
      , pomitra.idpodetail
      , ‘,pomitra.jumlah,0) ) AS variant_’
      , podetail.variant
      )
      )
      FROM pomitra inner join podetail on pomitra.idpodetail=podetail.idpodetail

      Kemudian dilanjutkan dengan query berikutnya

  • halo mas saya punya queri : SELECT * FROM view_data dengan hasil :
    +———+———–+————+————+———–+————+————–+
    | Test_id | Nama Test | Bobot_test | Jenis_test | Skor_test | Pelamar_id | Nama_pelamar |
    +———+———–+————+————+———–+————+————–+
    | 1 | Krampelin | 4 | 0 | 80 | 1 | Hendri |
    | 2 | Psikotest | 5 | 0 | 85 | 1 | Hendri |
    | 1 | Krampelin | 4 | 0 | 75 | 2 | Asep |
    | 2 | Psikotest | 5 | 0 | 85 | 2 | Asep |
    +———+———–+————+————+———–+————+————–+
    Gimana caranya agar tampil nama testnya kepinggir menjadi kolom dimana si nama testnya itu dinamis dari view

Silakan tinggalkan komentar

Newsletter

Jadilah yang pertama tahu berita terbaru dari Jagowebdev.com