Sintaks PIVOT dan Crosstab Vendor

Anda sudah mengetahui pivot CASE yang kompatibel lintas sistem. Namun, pewawancara juga ingin mengetahui apakah Anda dapat menggunakan operator pivot khusus produk ketika tersedia.

SQL Server menyediakan operator PIVOT khusus. PostgreSQL menyediakan fungsi crosstab dalam ekstensi tablefunc. Mengetahui keduanya beserta sisi-sisi yang perlu diwaspadai menunjukkan pengalaman di dunia nyata.

PIVOT SQL Server menerima tiga hal:

• Agregat atas kolom nilai.
• Klausa FOR yang menamai kolom yang nilainya menjadi kolom baru.
• Daftar IN berisi nilai yang akan diubah menjadi kolom.

Operator ini harus diterapkan pada tabel turunan yang hanya menampilkan kunci, kolom penyebar, dan nilai—tidak lebih.

Jebakan halus pada PIVOT yang diujikan pewawancara: pengelompokan dilakukan secara implisit. SQL Server mengelompokkan berdasarkan setiap kolom dalam sumber yang BUKAN kolom agregat atau kolom FOR.

Jadi, jika tabel turunan Anda tidak sengaja menyertakan kolom tambahan seperti order_id, pivot juga mengelompokkannya dan Anda mendapatkan jauh lebih banyak baris daripada yang diharapkan. Selalu batasi kueri bagian dalam hanya pada kunci, kolom penyebar, dan nilai.

Di SQL Server, nama kolom yang diputar adalah nilai literal dari data, yang dibungkus dalam kurung siku. Jika suatu nilai diawali angka atau mengandung spasi, kurung siku wajib digunakan.

Anda memilihnya menggunakan nama yang sama dalam kurung siku pada SELECT bagian luar. Inilah juga alasan PIVOT tidak dapat menangani nilai yang tidak diketahui tanpa SQL dinamis: daftar IN ditulis secara tetap.

PostgreSQL tidak memiliki kata kunci PIVOT. Sebagai gantinya, ekstensi tablefunc menyediakan crosstab, sebuah fungsi yang menerima teks SQL dan mengubah bentuk outputnya.

Anda harus mengaktifkan ekstensi tersebut terlebih dahulu. crosstab mengharapkan kueri sumber mengembalikan tepat tiga kolom: pengenal baris, kategori, dan nilai, dalam urutan tersebut.

Bagian crosstab yang paling rentan terhadap kesalahan adalah daftar definisi kolom AS ct(...) di bagian akhir. Anda harus mendeklarasikan sendiri nama dan jenis kolom output, dan keduanya harus sesuai dengan jumlah serta urutan kategori.

Jika suatu kategori tidak ada pada sebuah baris, crosstab mengisinya berdasarkan posisi, yang dapat membuat data tidak selaras kecuali Anda menggunakan bentuk dua argumen di bawah ini.

Untuk menghindari ketidakselarasan ketika beberapa baris tidak memiliki kategori tertentu, gunakan bentuk dua argumen. Kueri kedua mengembalikan daftar lengkap nilai kategori dalam urutan yang benar, sehingga crosstab mengetahui dengan tepat kolom tempat setiap nilai harus ditempatkan.

Inilah bentuk andal yang diharapkan pewawancara ketika kategori tersebar.

Jika pewawancara bertanya tentang MySQL, jawabannya langsung: MySQL tidak memiliki PIVOT maupun crosstab. Satu-satunya pilihan Anda adalah agregasi kondisional dengan CASE (atau bentuk singkat SUM(... ) + IF()).

Inilah alasan pola CASE yang kompatibel lintas sistem sangat dihargai: pola ini adalah pilihan paling dasar yang berfungsi di mana saja.

Permintaan laporan: "satu baris per wilayah, dengan kolom yang menghitung pesanan dalam setiap status." Di SQL Server, masukkan tabel turunan yang sudah dibatasi ke dalam PIVOT menggunakan COUNT.

Karena Anda menghitung kolom status itu sendiri, setiap baris status yang bukan NULL dalam suatu kelompok akan dihitung. SELECT bagian luar mencantumkan setiap status sebagai kolom dalam kurung siku. Ini adalah alternatif ringkas daripada menulis tiga ekspresi COUNT(CASE ...).

PIVOT dan crosstab memiliki keterbatasan utama yang sama dengan agregasi kondisional: kolom output harus diketahui saat Anda menulis kueri.

• SQL Server: daftar IN ditulis secara tetap.
• crosstab PostgreSQL: daftar definisi kolom ditulis secara tetap.

Keduanya tidak dapat menemukan kategori saat program berjalan. Untuk itu, Anda perlu membuat teks SQL secara dinamis.

Jawaban wawancara yang baik membandingkannya secara jujur:

• Agregasi CASE: kompatibel lintas sistem, mudah dibaca, dan berfungsi di setiap mesin database. Pilihan bawaan.
• SQL Server PIVOT: ringkas untuk banyak kolom, tetapi pengelompokan implisitnya sering mengejutkan orang.
• crosstab PostgreSQL: kuat tetapi panjang, memerlukan ekstensi dan daftar definisi kolom.

Jika ragu, gunakan agregasi kondisional dan sebutkan operator khusus produk sebagai alternatif.

Tentukan dengan tepat perilaku SQL Server PIVOT yang biasanya ditanyakan pewawancara.

Sintaks pivot khusus produk dalam satu tampilan:

• SQL Server: PIVOT (SUM(x) FOR col IN ([a],[b])), dengan GROUP BY implisit atas kolom yang tersisa.
• PostgreSQL: crosstab() dari tablefunc, yang memerlukan daftar definisi kolom; gunakan bentuk dua argumen untuk data yang tersebar.
• MySQL: keduanya tidak ada, gunakan CASE.
• Ketiganya memerlukan kolom yang sudah diketahui saat kueri ditulis.