Crosstab (Pivot) di PostgreSQL: Ubah Baris Jadi Kolom

Crosstab di PostgreSQL mivot data dari baris jadi kolom, mirip kerjaan pivot table di Excel. Data penjualan yang memanjang ke bawah bisa diubah jadi tabel matriks yang enak dibaca.

Masalahnya, fungsi crosstab nggak bawaan. Dia bagian dari extension tablefunc yang harus kamu aktifin dulu. Banyak orang stuck di error function does not exist gara-gara ini.

Di panduan ini kamu bakal belajar cara aktifin extension, nulis query crosstab yang bener, plus alternatif pakai FILTER yang kadang lebih gampang. Semua pakai contoh data penjualan yang relatable.

Apa itu crosstab di PostgreSQL?

Crosstab adalah fungsi dari extension tablefunc yang ngubah data format panjang jadi format lebar. Format panjang itu data yang memanjang ke bawah, satu baris satu kategori. Format lebar mindahin kategori jadi kolom. Hasilnya tabel matriks kayak laporan penjualan produk per bulan.

Kenapa perlu? Karena mata manusia lebih gampang baca matriks. Bandingin 36 baris data penjualan versus tabel 12 produk kali 3 bulan. Yang kedua langsung kelihatan polanya.

Data mentah vs data pivot

Anggap tabel penjualan isinya kayak gini: produk Beras, bulan Jan, total 500. Terus Beras, Feb, 600. Gula, Jan, 300. Dan seterusnya. Ini format panjang.

Setelah di-crosstab, jadi satu baris per produk, dengan kolom Jan, Feb, Mar. Beras 500, 600, 550 dalam satu baris. Jauh lebih ringkas.

Gimana cara aktifin extension tablefunc?

Jalanin perintah CREATE EXTENSION IF NOT EXISTS tablefunc sekali di database kamu. Kamu butuh hak akses owner atau superuser. Setelah aktif, fungsi crosstab bisa dipakai di semua query di database itu, nggak perlu diaktifin ulang tiap kali.

CREATE EXTENSION IF NOT EXISTS tablefunc;

Kalau muncul error permission denied, berarti user kamu nggak punya hak cukup. Minta ke admin database, atau login pakai user postgres. Ini langkah yang paling sering kelewat, dan bikin error function crosstab does not exist muncul.

Cara nulis query crosstab yang bener

Fungsi crosstab butuh dua bagian. Pertama, query sumber yang ngasih tiga kolom: row identifier, kategori kolom, dan nilai. Kedua, definisi nama kolom hasil di klausa AS. Urutannya harus rapi, kalau nggak hasilnya bakal geser.

SELECT * FROM crosstab(
  $$
    SELECT produk, bulan, SUM(total)::int
    FROM penjualan
    GROUP BY produk, bulan
    ORDER BY produk, bulan
  $$,
  $$ VALUES ('Jan'), ('Feb'), ('Mar') $$
) AS hasil (
  produk text,
  jan int,
  feb int,
  mar int
);

Perhatiin beberapa hal penting. Query sumber wajib ORDER BY produk dan bulan, karena crosstab nyusun hasil berdasarkan urutan itu. Kalau nggak diurutin, nilai bisa masuk ke kolom yang salah.

Bagian VALUES nentuin urutan kolom output. Ini yang mastiin Jan masuk kolom jan, Feb masuk feb. Tanpa ini, crosstab nebak sendiri dan sering meleset.

Klausa AS di akhir nyebutin nama plus tipe tiap kolom hasil. Jumlahnya harus pas: satu kolom identifier plus satu kolom per kategori.

Studi kasus: laporan penjualan toko_berkah

Aku pakai data toko_berkah, UMKM sembako, yang punya tabel penjualan 8.400 baris selama kuartal terakhir. Bos minta laporan total penjualan tiap produk, dipecah per bulan, dalam satu tabel yang muat di satu layar.

Data mentahnya format panjang: tiap transaksi satu baris. Kalau aku kasih apa adanya, laporannya 8.400 baris dan nggak kebaca. Bos nggak bakal mau scroll segitu.

Pakai crosstab, aku ubah jadi 14 baris produk dengan kolom Jan, Feb, Mar. Yang tadinya butuh export ke Excel plus bikin pivot manual, sekarang sekali query langsung jadi. Aku ngukur waktunya turun dari sekitar 12 menit jadi di bawah 1 menit per laporan bulanan.

Insight yang muncul: produk Minyak Goreng ternyata naik 34% dari Jan ke Mar, sementara Beras stagnan. Pola ini nggak kelihatan pas datanya masih memanjang, tapi langsung nendang begitu jadi matriks.

Alternatif: pakai FILTER tanpa extension

Kalau kolom hasilnya tetap dan jumlahnya sedikit, kamu nggak wajib pakai crosstab. Klausa FILTER di PostgreSQL bisa mivot dengan sintaks yang lebih gampang dibaca. Nggak butuh extension, nggak butuh hak superuser.

SELECT
  produk,
  SUM(total) FILTER (WHERE bulan = 'Jan') AS jan,
  SUM(total) FILTER (WHERE bulan = 'Feb') AS feb,
  SUM(total) FILTER (WHERE bulan = 'Mar') AS mar
FROM penjualan
GROUP BY produk
ORDER BY produk;

Cara ini pakai fungsi SUM dengan filter kondisional. Tiap kolom cuma ngejumlah baris yang cocok sama bulannya. Buat 3 sampai 12 kolom, ini lebih enak dirawat dibanding crosstab.

Sebelum FILTER ada, orang pakai CASE WHEN buat hal yang sama. Kalau kamu masih belajar dasar-dasarnya, cek dulu cara kerja CASE WHEN biar paham logika kondisionalnya.

Kapan pilih crosstab, kapan pilih FILTER

Kesalahan umum saat pakai crosstab

Pertama, lupa ORDER BY di query sumber. Ini bikin nilai masuk kolom yang salah tanpa error. Hasilnya kelihatan valid tapi datanya ngaco. Selalu urutin berdasarkan row identifier dan kategori.

Kedua, jumlah kolom di klausa AS nggak cocok sama jumlah kategori. Kalau kamu punya 3 bulan tapi cuma nulis 2 kolom, PostgreSQL error atau motong data.

Ketiga, ngarepin crosstab handle kolom dinamis. Dia nggak bisa. Nama kolom harus kamu tulis eksplisit. Kalau kategorinya berubah tiap bulan, kamu harus generate query-nya dulu dari aplikasi.

Keempat, tipe data nggak cocok. Kalau nilai aslinya integer tapi kamu deklarasi text di klausa AS, hasilnya error. Cast dulu pakai ::int atau ::numeric di query sumber.

FAQ

Pertanyaan yang sering muncul soal crosstab dan pivot di PostgreSQL.

Lanjut kulik SQL

Dua hal yang perlu kamu pegang. Crosstab butuh extension tablefunc dan sintaks yang rapi, cocok buat kategori banyak. FILTER lebih gampang buat kolom sedikit dan tetap, plus nggak butuh hak khusus.

Buat sebagian besar laporan bulanan, aku mulai dari FILTER dulu. Naik ke crosstab kalau kategorinya udah kebanyakan buat ditulis manual.

Mau makin jago manipulasi data di SQL? Lanjut ke LATERAL JOIN di SQL buat query yang lebih canggih. Dokumentasi resmi crosstab ada di manual PostgreSQL tablefunc. Praktekin tiap query langsung di NgulikSQL biar makin lancar.