Menulis kueri yang mempivot dan membatalkan pivot tataan hasil
Gunakan pivot dalam SQL Server untuk memutar cara data ditampilkan dari orientasi berbasis baris menjadi orientasi berbasis kolom. Saat mempivot, Anda mengonsolidasikan nilai dalam kolom ke daftar nilai yang berbeda, lalu memproyeksikan daftar itu sebagai judul kolom. Biasanya, ini termasuk agregasi ke nilai kolom di kolom baru.
Misalnya, data sumber parsial di bawah ini mencantumkan nilai berulang untuk Category dan Orderyear, bersama dengan nilai untuk Qty, untuk setiap contoh pasangan Category/Orderyear:
| Category | Jml | Orderyear |
|---|---|---|
| Produk Susu | 12 | 2019 |
| Biji-bijian/Sereal | 10 | 2019 |
| Produk Susu | 5 | 2019 |
| Makanan Laut | 2 | 2020 |
| Makanan Manis | 36 | 2020 |
| Bahan Penyedap | 35 | 2020 |
| Makanan Manis | 55 | 2020 |
| Bahan Penyedap | 16 | 2020 |
| Produk Susu | 60 | 2020 |
| Produk Susu | 20 | 2020 |
| Makanan Manis | 24 | 2020 |
| ...(2155 baris terpengaruh) |
Tabel di atas mewakili lebih dari 2000 baris, dengan banyak nilai duplikat. Untuk menganalisis hasil berdasarkan kategori dan tahun, Anda mungkin ingin mengatur nilai yang akan ditampilkan sebagai berikut, menjumlahkan kolom Qty di sepanjang tabel:
| Category | 2019 | 2020 | 2021 |
|---|---|---|---|
| Minuman | 1842 | 3996 | 3694 |
| Bahan Penyedap | 962 | 2895 | 1441 |
| Makanan Manis | 1357 | 4137 | 2412 |
| Produk Susu | 2086 | 4374 | 2689 |
| Biji-bijian/Sereal | 549 | 2636 | 1377 |
| Daging/Unggas | 950 | 2189 | 1060 |
| Hasil Kebun | 549 | 1583 | 858 |
| Makanan Laut | 1286 | 3679 | 2716 |
| (8 baris terpengaruh) |
Hasilnya sekarang berjumlah total delapan baris. Dalam proses mempivot, setiap tahun yang berbeda dibuat sebagai header kolom, dan nilai dalam kolom Qty dikelompokkan berdasarkan Category dan teragregasi.
Menggunakan PIVOT untuk mempivot hasil
Anda dapat mempivot tataan hasil menggunakan operator PIVOT. Operator tabel Pivot T-SQL bekerja pada output klausul FROM dalam pernyataan SELECT. Untuk menggunakan PIVOT, Anda perlu menyediakan tiga elemen ke operator:
- Pengelompokan: dalam klausul FROM, Anda menyediakan kolom input. Dari kolom tersebut, PIVOT akan menentukan kolom mana yang akan digunakan untuk mengelompokkan data untuk agregasi. Ini didasarkan pada kolom mana yang tidak digunakan sebagai elemen lain di operator PIVOT.
- Penyebaran: Anda menyediakan daftar nilai yang dipisah koma untuk digunakan sebagai judul kolom untuk data yang telah dipivotkan. Nilainya harus terjadi dalam data sumber.
- Agregasi: Anda menyediakan fungsi agregasi (SUM, dan seterusnya) yang akan dilakukan pada baris yang dikelompokkan.
Selain itu, Anda perlu menetapkan alias tabel ke tabel hasil operator PIVOT. Contoh berikut menunjukkan elemen di tempat:
SELECT Category, [2019],[2020],[2021]
FROM ( SELECT Category, Qty, Orderyear FROM Sales.CategoryQtyYear) AS D
PIVOT(SUM(qty) FOR orderyear IN ([2019],[2020],[2021])) AS pvt;
Dalam contoh di atas, Orderyear adalah kolom yang menyediakan nilai penyebaran, Qty digunakan untuk agregasi, dan Category untuk pengelompokan. Nilai Orderyear dilampirkan dalam pemisah untuk menunjukkan bahwa mereka adalah pengidentifikasi kolom dalam hasilnya.
Gunakan UNPIVOT untuk membatalkan pivot tataan hasil
Membatalkan pivot data adalah kebalikan logis dari mempivot data. Bukannya mengubah baris menjadi kolom, membuka pivot mengubah kolom menjadi baris. Ini adalah teknik yang berguna dalam mengambil data yang telah dipivot (dengan atau tanpa menggunakan operator PIVOT T-SQL) dan mengembalikannya ke tampilan bertabel berorientasi baris. Anda dapat menggunakan operator tabel UNPIVOT untuk menyelesaikan hal ini.
Untuk menggunakan operator UNPIVOT, Anda menyediakan tiga elemen:
- Kolom sumber yang akan dibuka pivot-nya.
- Nama untuk kolom baru yang akan menampilkan nilai yang tidak terpivot.
- Nama untuk kolom yang akan menampilkan nama nilai yang tidak terpivot.
Contoh berikut menentukan 2019, 2020, dan 2021 sebagai kolom yang akan dibatalkan pivotnya, menggunakan nilai nama kolom baru orderyear dan qty yang akan ditampilkan dalam kolom qty baru.
SELECT category, qty, orderyear
FROM Sales.PivotedCategorySales
UNPIVOT(qty FOR orderyear IN([2019],[2020],[2021])) AS unpvt;
Saat membatalkan pivot data, satu atau beberapa kolom didefinisikan sebagai sumber yang akan dikonversi menjadi baris. Data dalam kolom tersebut tersebar, atau dibagi, menjadi satu atau beberapa baris baru, tergantung pada berapa banyak kolom yang dibatalka pivotnya. Dalam data sumber berikut, tiga kolom tidak akan dibatalkan pivotnya. Setiap nilai Orderyear akan disalin ke baris baru dan dikaitkan dengan nilai Category. NULL apa pun akan dihapus dalam proses dan tidak ada baris yang dibuat:
| Category | 2019 | 2020 | 2021 |
|---|---|---|---|
| Minuman | 1842 | 3996 | 3694 |
| Bahan Penyedap | 962 | 2895 | 1441 |
| Makanan Manis | 1357 | 4137 | 2412 |
| Produk Susu | 2086 | 374 | 2689 |
| Biji-bijian/Sereal | 549 | 2636 | 1377 |
| Daging/Unggas | 950 | 2189 | 1060 |
| Hasil Kebun | 549 | 1583 | 858 |
| Makanan Laut | 1286 | 3679 | 2716 |
Untuk setiap persimpangan Category dan Orderyear, baris baru akan dibuat, seperti dalam hasil parsial ini:
| category | qty | tahun pesanan |
|---|---|---|
| Minuman | 1842 | 2019 |
| Minuman | 3996 | 2020 |
| Minuman | 3694 | 2021 |
| Bahan Penyedap | 962 | 2019 |
| Bahan Penyedap | 2895 | 2020 |
| Bahan Penyedap | 1441 | 2021 |
| Makanan Manis | 1357 | 2019 |
| Makanan Manis | 4137 | 2020 |
| Makanan Manis | 2412 | 2021 |
Membatalkan pivot tidak mengembalikan data asli. Data tingkat detail hilang selama proses agregasi di pivot asli. UNPIVOT tidak memiliki kemampuan untuk mengalokasikan nilai untuk kembali ke nilai detail asli.