Cara Membuat Pivot Table di MySQL (Statis dan Dinamis)

Pivot table adalah teknik meringkas data dengan cara mengubah baris menjadi kolom. Kalau Anda pernah memakai fitur PivotTable di Excel, konsepnya sama: data yang tersusun memanjang ke bawah “diputar” menjadi tabel silang yang jauh lebih mudah dibaca. MySQL tidak punya perintah PIVOT khusus seperti SQL Server, tetapi kita bisa mencapainya dengan agregasi kondisional.

Artikel ini membahas dua pendekatan: pivot statis (jumlah kolom sudah diketahui) dan pivot dinamis (kolom dibuat otomatis mengikuti isi data).

Data Awal

Kita pakai tabel penjualan sederhana yang mencatat penjualan tiap produk per bulan.

CREATE TABLE penjualan (
    id INT PRIMARY KEY AUTO_INCREMENT,
    produk VARCHAR(40),
    bulan VARCHAR(10),
    jumlah INT
);

INSERT INTO penjualan (produk, bulan, jumlah) VALUES
('Kopi',  'Januari',  120),
('Kopi',  'Februari', 150),
('Teh',   'Januari',   80),
('Teh',   'Februari',  95),
('Gula',  'Januari',  200),
('Gula',  'Februari', 210);

Dalam bentuk aslinya, data ini “vertikal” — satu baris per kombinasi produk dan bulan. Sulit dibaca kalau ingin membandingkan penjualan antar-bulan. Tujuan kita: menjadikan tiap bulan sebagai kolom.

Pivot Statis dengan CASE WHEN

Bila daftar bulannya sudah pasti (Januari dan Februari), kita bisa menulis satu kolom untuk tiap bulan menggunakan SUM(CASE WHEN ...):

SELECT
    produk,
    SUM(CASE WHEN bulan = 'Januari'  THEN jumlah ELSE 0 END) AS januari,
    SUM(CASE WHEN bulan = 'Februari' THEN jumlah ELSE 0 END) AS februari,
    SUM(jumlah) AS total
FROM penjualan
GROUP BY produk;

Hasilnya berubah menjadi tabel silang yang rapi:

Perhatikan pola pentingnya: GROUP BY produk menentukan baris pivot, sedangkan tiap ekspresi CASE WHEN menentukan sebuah kolom. Anda juga bisa memakai SUM(IF(bulan='Januari', jumlah, 0)) yang lebih singkat — hasilnya identik.

Kelemahan Pivot Statis

Cara di atas bekerja sempurna selama daftar kolomnya tetap. Masalah muncul kalau bulan bertambah (Maret, April, …) — Anda harus mengedit query dan menambah baris CASE WHEN secara manual setiap kali. Untuk data yang kategorinya berubah-ubah, kita butuh pivot dinamis.

Pivot Dinamis dengan GROUP_CONCAT

Ide pivot dinamis: menyusun query pivot secara otomatis berdasarkan nilai unik yang ada di data. Caranya, kita rangkai teks SQL memakai GROUP_CONCAT, simpan ke sebuah variabel, lalu jalankan sebagai prepared statement.

Langkah 1 — bangun potongan SQL untuk setiap bulan secara otomatis:

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
        'SUM(CASE WHEN bulan = ''', bulan, ''' THEN jumlah ELSE 0 END) AS `', bulan, '`'
    )
) INTO @sql
FROM penjualan;

Variabel @sql kini berisi rangkaian ekspresi kolom untuk semua bulan yang ada di tabel — tanpa perlu kita tulis manual.

Langkah 2 — bungkus menjadi query lengkap:

SET @sql = CONCAT(
    'SELECT produk, ', @sql, ', SUM(jumlah) AS total
     FROM penjualan
     GROUP BY produk'
);

Langkah 3 — jalankan melalui prepared statement:

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

Sekarang, berapa pun jumlah bulan yang masuk ke tabel, kolom pivot akan menyesuaikan secara otomatis. Inilah keunggulan pivot dinamis.

Membungkusnya dalam PHP

Di aplikasi nyata, ketiga langkah tadi biasanya dijalankan dari PHP. Berikut kerangkanya memakai PDO:

$pdo->exec("SET @sql = NULL");
$pdo->exec("SELECT GROUP_CONCAT(DISTINCT
    CONCAT('SUM(CASE WHEN bulan = ''', bulan, ''' THEN jumlah ELSE 0 END) AS `', bulan, '`')
) INTO @sql FROM penjualan");
$pdo->exec("SET @sql = CONCAT('SELECT produk, ', @sql,
    ', SUM(jumlah) AS total FROM penjualan GROUP BY produk')");
$pdo->exec("PREPARE stmt FROM @sql");
$data = $pdo->query("EXECUTE stmt")->fetchAll(PDO::FETCH_ASSOC);
$pdo->exec("DEALLOCATE PREPARE stmt");

foreach ($data as $baris) {
    print_r($baris);
}

Catatan Keamanan

Karena pivot dinamis menyusun SQL dari isi data, waspadai risiko SQL injection bila nilai kategori berasal dari input pengguna. Pastikan kolom yang dijadikan header berasal dari data yang tepercaya (misalnya daftar bulan atau kategori baku), atau saring nilainya lebih dulu. Untuk data internal seperti nama bulan, risiko ini praktis nol.

Contoh Kasus Nyata: Rekap Absensi Karyawan

Pivot table sangat cocok untuk laporan absensi. Bayangkan tabel absensi berisi kolom nama, tanggal, dan status (hadir/izin/sakit). Dengan pivot, Anda bisa menampilkan satu baris per karyawan dengan kolom jumlah Hadir, Izin, dan Sakit:

SELECT
    nama,
    SUM(status = 'hadir') AS hadir,
    SUM(status = 'izin')  AS izin,
    SUM(status = 'sakit') AS sakit
FROM absensi
GROUP BY nama;

Laporan yang tadinya butuh pengolahan manual di Excel kini selesai dalam satu query.

Penutup

Pivot table adalah senjata ampuh untuk menyulap data mentah menjadi laporan yang enak dibaca. Gunakan pivot statis (CASE WHEN) bila kolomnya tetap, dan pivot dinamis (GROUP_CONCAT + prepared statement) bila kategorinya berubah-ubah. Setelah terbiasa, Anda akan sering memakainya untuk berbagai laporan ringkas.

Referensi: untuk pendalaman lebih lanjut, kunjungi dokumentasi resmi MySQL.

Baca Juga

• Cara Menghitung Total dan Subtotal di MySQL dengan WITH ROLLUP
• Prefix Tabel pada Database MySQL, Perlukah Digunakan?
• Cara Koneksi PHP ke Database MySQL (mysqli & PDO Lengkap)