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 menjadiTOTAL
. Karena kita menggunakan klausaWITH ROLLUP
, maka kolom nama pada baris total berisi nilaiNULL
, agar lebih mudah dibaca, kita ganti nilaiNULL
tersebut dengan kata-kataTOTAL
.
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 fungsiCOUNT
untuk menghitung jumlah baris pada kolomtgl_trx
(baris 4-7). - Pada variabel
@sql
, kita tambahkanIFNULL(nama, "TOTAL") AS nama_sales
(baris 17) untuk mengubah nilaiNULL
pada kolom nama_sales menjadiTOTAL
- Kita tambahkan
COUNT(tgl_trx) AS JML_TRX
(baris 19) untuk menambahkan kolomJML_TRX
yang berada di bagian kanan. - Terakhir, kita tambahkan
SUM(nilai_trx) AS TOTAL
(baris 20) untuk menambahkan kolomTOTAL
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
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:
Hasil:
Query Dinamisnya:
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
Pertanyaannya apa ya mas?
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:
Untuk query dinamisnya bisa dicoba seperti ini mas:
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.
Bisa mas, saya juga menggunakan XAMPP
Permisi mas, mau nanya
Kalau mau menjalankannya di php bagaimana caranya?
Saya baca beberapa ada yang bilang menggunakan fungsi CALL
Fungsi call untuk memanggil stored procedure mas, digunakan jika pivot table tersebut di buat menggunakan stored procedure
mas agus saya sudah berhasil membuat privot static saat saya convert ke privot dinamis kok error ya mas
query berhasil
kode dinamisnya
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
Pakai phpMyAdmin nya versi berapa mas?
salam, maaf mau tanya pa.. saya menampilkan yang seperti contoh ini, script query yang saya buat begini:
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?
hilangkan tanda kutip di depan MAX mas…
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:
Mohon bantuannya ya mas agus, Terimakasih sebelumnya.
Sama sama mas, itu mungkin nilai b.id nya berbeda mas antara baris satu dengan baris ke dua
coba ditampilkan b.id nya SELECT b.id, a.id, dst…
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:
Tabel Target:
Tabel Jns_Rpkd:
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:
Selanjutnya:
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 :
Mohon pencerahan querynya mas,,, thx
Itu seharusnya yang dijumlahkan yang ID nya berapa mas? kalau ID nya 1,2, dan 4 jumlahnya 7.500, kalau ID nya 1 dan 3 jumlahnya 3.500
terima kasih pak. sangat membantu.
bagaimana jika ingin menampilkan data dicrosstab itu berupa huruf, contoh :
Tergantung data awalnya mas, apakah berupa angka dikonversi ke huruf? atau sudah berupa huruf?
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
Coba pelajari ini mas:
http://jagowebdev.com/pivot-table-dengan-php-dan-mysql/
Kalau mau order header pivotnya gmn ya berdasarkan tanggal (bulan & tahun) ?
Di bagian SQL dinamis nya bisa ditambahkan klausa order by mas…
Mas, kalo misal eksekusinya bukan di jumlah (SUM).. Apakh bisa kita gunakan function yg di dalamnya mengandung rumus??
Seharusnya bisa mas…
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:
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
Bisa mas, coba dibaca artikel yang ini mas: http://jagowebdev.com/pivot-table-dengan-php-dan-mysql/
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
Berarti nama programnya yang dibuat dinamis mas….
mas saya error terus di prepare stmt FROM @sql; , gimana solusinya mas ? trima kasih ,,
Coba dipecah dan dijalankan query nya satu per satu mas…
Mas saya mau tanya tentang error dalam query ini :
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 ?
Coba kolom nm_divisi pada @sql_dinamis ditambah tanda kutip 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 |
Maksudnya per tanggal bayar bagaimana ya mas? apa tanggal bayarnya dijadikan kolom?
Problem pivot table terselesaikan dengan bantuan artikel mas agus, untuk masalah yang komplek query dibentuk di php, lalu dipanggil ke mysql, salam hormat suhu
Siap sama sama mas…
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
Maksudnya seperti apa mas? kalau layout tabel outputnya sama Querynya juga sama mas…
pengennya saya kaya gini mas totalnya, gimana cara buat NULL pada kolom keduanya mas ? trima kasih
Untuk tampilannya bisa dilakukan di sisi aplikasi mas, misal if ($row[‘nama_sales’] == ”) { … }
Pagi Mas maaf sebelumnya,
untuk syntax diatas menggunakan mysql jika ke Postgresql bagaimana ya karena ketentuan di postgre sangat berbeda sekali ya… terima kasih..
Maaf untuk PostgreSql belum ada mas…
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:
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
Coba cek SQL yang ada di variabel @sql mas: SELECT @sql;
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
Coba di cek isi variabel SQL nya mas: SELECT @SQL hasilnya coba di jalankan di workbench errornya dimana
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.
Sama sama mas. Iya mas di webdevzoom versi translate nya 😀
MANTAF TUTORNYA DAN SANGAN MEMBANTU…TAPI YANG SAYA MASIH BINGUN KLU PIVOT TABLEL DINAMIS DI IMPLEMENTASIKAN DENGAN PHP MAS…MOHON PENCERAHANNYA
Di php tinggal dijalankan querynya mas, misal mysql_query(“sql pivot table”)
OK MAS THANK BANGET…BERHASIL
Sama sama mas…
MAS MISAL SAYA PUNYA DATA SEPERTI DIATAS , TRUS SAYA MAU PIVOT MENJADI SEPERTI INI
SAYA COBA PAKE PIVOT DINAMIS TAPI MASIH ERROR
Error nya bagaimana mas? atau bisa dicoba di print @sql nya, misal SELECT @sql kemudian query dalam @sql tersebut di eksekusi tersendiri
sdh sy coba lg nggak error mas…cuma errornya lg klu datanya banyak..kan saya punya data 3jt..
Error nya kenapa mas? 3jt belum terlalu banyak, coba pakai index dulu
#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
Coba variabel @sql nya dicetak mas, Misal SELECT @sql terus dijalankan sql tersebut, error nya di sebelah mana
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
Coba di tampilkan variabel @sql nya (SELECT @sql) kemudian dicek querynya
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
Coba di tampilkan isi variabel @sql ya mas, SELECT @sql kemudian jalankan di phpmyadmin, cek errornya
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.
Sama sama 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.
Querynya tinggal dimasukkan ke script php mas, coba pelajari artikel:
Pivot Table Dengan PHP dan MySQL
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
Ada contoh tabel hasilnya seperti apa mas?
Cara query sql dinamis di code igniter 3 gimana ya ?
Sama saja mas, tinggal di definisikan querynya, misal: $sql = “SELECT…” querynya dapat di tes terlebih dahulu di database manager seperti phpmyadmin