Coding Interview Prep · Pelajaran

PIVOT Dinamis dengan Kolom yang Belum Diketahui

Membuat kolom pivot saat kategori belum diketahui sebelumnya.

Pelajaran 4 dari 413 langkah

PIVOT Dinamis dengan Kolom yang Belum Diketahui adalah pelajaran Coding Interview Prep gratis di CoddyKit. Ini adalah pelajaran 4 dari 4. Kamu bisa membaca pelajaran lengkapnya di bawah secara gratis — lalu praktikkan langsung di browser dengan editor kode bawaan dan tutor AI 24/7. Ini adalah bagian dari jalur belajar Coding Interview Prep, dan progresmu tersinkronisasi di web dan aplikasi CoddyKit. Kursus Coding Interview Prep mencakup 4 pelajaran total.

Pertanyaan Sulit tentang Pemutaran Data

Setiap pemutaran data statis, baik menggunakan agregasi CASE, PIVOT SQL Server, maupun crosstab PostgreSQL, memiliki satu keterbatasan yang sama: Anda harus mencantumkan kolom keluaran saat menulis kueri.

Namun, bagaimana jika kategorinya belum diketahui, seperti nama produk yang berubah setiap minggu atau satu kolom untuk setiap bulan aktif? Itulah pemutaran data dinamis, dan ini menjadi pertanyaan wawancara tingkat senior karena SQL biasa tidak dapat mengembalikan hasil yang daftar kolomnya ditentukan saat waktu proses.

Mengapa SQL Saja Tidak Dapat Melakukannya

SQL memiliki tipe statis pada tingkat kumpulan hasil: perencana harus mengetahui kolom dan tipenya sebelum eksekusi. Satu kueri tidak dapat mengatakan buat satu kolom untuk setiap nilai yang kebetulan Anda temukan.

Jadi, teknik universalnya adalah membuat teks SQL dalam dua langkah: pertama, kueri kategori yang berbeda; kemudian, buat string kueri pemutusan data dari kategori tersebut dan jalankan string itu.

Langkah 1: Kumpulkan Kategori

Langkah pertama adalah kueri biasa yang mencantumkan nilai berbeda yang akan menjadi kolom. Biasanya, Anda mengurutkannya agar tata letak kolom tetap konsisten.

Hasil ini menjadi masukan untuk langkah penyusunan string. Dalam sistem nyata, Anda menjalankan langkah ini, menyimpan barisnya, lalu menyusun kueri berikutnya dari baris tersebut.

SELECT DISTINCT quarter
FROM sales
ORDER BY quarter;
-- e.g. Q1, Q2, Q3, Q4

Langkah 2: Buat Daftar Kolom

Berikutnya, ubah nilai-nilai tersebut menjadi daftar ekspresi CASE yang dipisahkan koma (atau nama dalam tanda kurung siku untuk PIVOT). Basis data menyediakan fungsi agregasi string untuk melakukan hal ini langsung dalam SQL.

Di PostgreSQL, fungsinya adalah string_agg; di MySQL, GROUP_CONCAT; di SQL Server, STRING_AGG atau trik lama FOR XML PATH.

-- Postgres: build the SELECT-list fragment
SELECT string_agg(
  format('SUM(CASE WHEN quarter = %L THEN amount END) AS %I',
         quarter, quarter),
  ', '
)
FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;

Langkah 3: Rangkai dan Jalankan

Gabungkan fragmen yang dihasilkan ke dalam string kueri lengkap, lalu jalankan dengan eksekusi dinamis: EXECUTE dalam PL/pgSQL, sp_executesql di SQL Server, atau PREPARE/EXECUTE di MySQL.

Inilah inti pemutaran data dinamis: SQL menulis SQL, lalu menjalankannya.

-- SQL Server pattern
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(quarter), ',')
FROM (SELECT DISTINCT quarter FROM sales) q;
SET @sql = N'SELECT region, ' + @cols + '
  FROM (SELECT region, quarter, amount FROM sales) s
  PIVOT (SUM(amount) FOR quarter IN (' + @cols + ')) p;';
EXEC sp_executesql @sql;

Contoh Lengkap PostgreSQL

Di PostgreSQL, Anda membungkus ketiga langkah tersebut dalam blok DO atau fungsi. Buat daftar kolom dengan string_agg, sisipkan daftar itu ke dalam kueri, lalu jalankan dengan EXECUTE.

Karena kolom hasil belum diketahui hingga waktu proses, fungsi yang mengembalikan hasil seperti ini sering menggunakan RETURNS SETOF record atau mengembalikan baris sebagai json, yang kemudian diperluas oleh pemanggilnya.

DO $do$
DECLARE
  cols text;
  qry  text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN quarter=%L THEN amount END) AS %I', quarter, quarter), ', ')
  INTO cols
  FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

MySQL dengan Pernyataan yang Disiapkan

MySQL tidak memiliki operator pemutaran data, jadi pemutaran data dinamis membuat string agregasi bersyarat dengan GROUP_CONCAT, lalu menjalankannya melalui pernyataan yang disiapkan.

GROUP_CONCAT memiliki batas panjang (group_concat_max_len) yang mungkin disebutkan pewawancara; naikkan nilainya jika kategorinya banyak.

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT('SUM(CASE WHEN quarter=''', quarter,
         ''' THEN amount END) AS ', QUOTE(quarter))
) INTO @sql FROM sales;
SET @sql = CONCAT('SELECT region, ', @sql,
                  ' FROM sales GROUP BY region');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

Risiko Injeksi SQL

Karena Anda menggabungkan nilai data ke dalam SQL yang dapat dijalankan, pemutaran data dinamis memiliki risiko injeksi. Jika nilai kategori berisi tanda kutip atau teks berbahaya, nilai tersebut dapat merusak atau mengambil alih kueri yang dihasilkan.

Selalu lakukan pelolosan pada pengenal dan literal dengan pembantu aman dari mesin basis data: format('%I', ...) dan %L di PostgreSQL, serta QUOTENAME di SQL Server. Jangan pernah menempelkan nilai mentah langsung ke dalam string.

-- Safe quoting prevents injection / breakage
-- Postgres: %I identifier, %L literal
format('SUM(CASE WHEN k=%L THEN v END) AS %I', cat, cat)
-- SQL Server: QUOTENAME(cat)

Mengembalikan Kolom yang Belum Diketahui

Bagian sulit kedua: pemanggil tidak dapat mengetahui bentuk hasil sebelumnya. Strategi umum yang diterima pewawancara:

Kembalikan baris sebagai JSON dan biarkan lapisan aplikasi memperluas kuncinya.
Minta prosedur menampilkan atau membuat kueri, lalu jalankan kueri tersebut sebagai langkah kedua.
Lakukan pemutaran data akhir dalam kode aplikasi (pustaka analisis data atau alat BI) setelah kategorinya diketahui.

Tidak ada cara yang bersih untuk mengembalikan kolom sembarang dari satu pemanggilan statis.

Contoh Kerja: Pemutaran Berdasarkan Produk

Misalkan produk terus bermunculan dan menghilang, sementara laporan memerlukan satu kolom pendapatan untuk setiap produk yang saat ini ada di sales. Anda tidak dapat menetapkan daftarnya secara tetap, jadi buat daftar tersebut secara dinamis. PostgreSQL membuatnya mudah dibaca: buat fragmen CASE dengan string_agg dan pengutipan aman, sisipkan fragmen itu ke dalam kueri, lalu jalankan dengan EXECUTE.

Jelaskan langkahnya kepada pewawancara: temukan produk, ubah masing-masing menjadi kolom yang diberi tanda kutip, rangkai, lalu jalankan. Pola yang sama berlaku di mesin basis data apa pun; hanya fungsi pembantunya yang berubah.

DO $do$
DECLARE cols text; qry text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN product=%L THEN amount END) AS %I',
           product, product), ', ')
  INTO cols
  FROM (SELECT DISTINCT product FROM sales ORDER BY 1) p;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

Kapan Menghindari Pemutaran Data Dinamis

Kandidat yang kuat tahu kapan tidak melakukannya dalam SQL. SQL dinamis lebih sulit dibaca, diuji, diamankan, dan disimpan dalam tembolok. Sering kali jawaban yang lebih baik adalah:

Kembalikan bentuk panjang dari SQL, lalu lakukan pemutaran data di aplikasi atau lapisan pelaporan.
Jika kumpulan kategorinya kecil dan jarang berubah, gunakan pemutaran data statis dan perbarui sesekali.

Gunakan pemutaran data dinamis hanya untuk kumpulan kategori yang benar-benar terbuka dan terus berubah.

Pemeriksaan Singkat

Uji alasan utama adanya pemutaran data dinamis.

Jawab di pelajaran interaktif

Ringkasan

Pemutaran data dinamis menangani kumpulan kolom yang belum diketahui:

Pemutaran data statis gagal karena kolom hasil harus ditetapkan sebelum eksekusi.
Pola: kueri kategori yang berbeda, buat string SQL pemutaran data, lalu jalankan secara dinamis.
Gunakan string_agg/GROUP_CONCAT/STRING_AGG untuk membuat daftar kolom.
Lakukan pelolosan pada nilai (%I/%L, QUOTENAME) untuk mencegah injeksi SQL.
Sering kali lebih bersih jika bentuk panjang dikembalikan dan pemutaran data dilakukan di lapisan aplikasi.

Gratis untuk memulai

Belajar Coding Interview Prep dengan tutor AI — gratis

Tulis dan jalankan kode asli di browser kamu, dapatkan bantuan instan dari tutor AI 24/7, dan lanjutkan di mana kamu tinggalkan di web atau aplikasi.

Kursus: 90
Pelajaran: 360

Mulai belajar gratis Lihat kursus lengkapnya

Pertanyaan yang Sering Diajukan

Apakah pelajaran “PIVOT Dinamis dengan Kolom yang Belum Diketahui” gratis?

Ya — teks lengkap “PIVOT Dinamis dengan Kolom yang Belum Diketahui” gratis dibaca di sini di web. Untuk praktiknya secara interaktif (editor kode bawaan dan tutor AI 24/7) dan buka sisa kursus Coding Interview Prep, upgrade ke CoddyKit PRO. Kursus Coding Interview Prep mencakup 4 pelajaran total.

Apa yang akan aku pelajari di “PIVOT Dinamis dengan Kolom yang Belum Diketahui”?

Membuat kolom pivot saat kategori belum diketahui sebelumnya. Kamu berlatih Coding Interview Prep dengan kode praktik yang langsung kamu jalankan di browser, dan tutor AI 24/7 menjawab pertanyaanmu saat kamu mengerjakan pelajaran ini.

Apakah aku perlu pengalaman untuk memulai Coding Interview Prep?

Tidak diperlukan pengalaman sebelumnya. Coding Interview Prep di CoddyKit dirancang untuk pemula hingga pelajar tingkat lanjut, jadi kamu bisa memulai di sini atau dari awal dan belajar sesuai kecepatan kamu sendiri. Ini adalah pelajaran 4 dari 4.

Berapa lama pelajaran “PIVOT Dinamis dengan Kolom yang Belum Diketahui” memakan waktu?

Sebagian besar pelajaran CoddyKit memakan waktu sekitar 5–10 menit. Setiap pelajaran ringkas dan interaktif, jadi kamu membuat kemajuan stabil dan melanjutkan dari tempat kamu tinggalkan di web dan aplikasi.

Bisakah aku menulis dan menjalankan kode dalam pelajaran Coding Interview Prep ini?

Ya. Setiap pelajaran Coding Interview Prep menyertakan editor kode bawaan, jadi kamu menulis dan menjalankan kode nyata langsung di browser dan mendapatkan umpan balik AI instan — tidak diperlukan penyiapan lokal.

Semua pelajaran dalam kursus ini

Mengubah Baris Menjadi Kolom dengan Agregasi Bersyarat
Sintaks PIVOT dan Crosstab Vendor
Mengubah Kolom Menjadi Baris
PIVOT Dinamis dengan Kolom yang Belum Diketahui

← Kembali ke Coding Interview Prep