DARI - Menggunakan PIVOT dan UNPIVOT
Berlaku untuk:
SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
Anda dapat menggunakan operator relasional PIVOT dan UNPIVOT untuk mengubah ekspresi bernilai tabel ke tabel lain. PIVOT memutar ekspresi bernilai tabel dengan mengubah nilai unik dari satu kolom dalam ekspresi menjadi beberapa kolom dalam output. Dan PIVOT menjalankan agregasi di mana nilai tersebut diperlukan pada nilai kolom yang tersisa yang diinginkan dalam output akhir. UNPIVOT melakukan operasi yang berlawanan dengan PIVOT dengan memutar kolom ekspresi bernilai tabel ke dalam nilai kolom.
Sintaks untuk PIVOT menyediakan lebih sederhana dan lebih mudah dibaca daripada sintaks yang mungkin ditentukan dalam serangkaian SELECT...CASE pernyataan yang kompleks. Untuk deskripsi lengkap sintaks untuk PIVOT, lihat FROM (Transact-SQL).
Sintaks
Sintaks berikut meringkas cara menggunakan PIVOT operator.
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
Keterangan
Pengidentifikasi kolom dalam UNPIVOT klausul mengikuti kolate katalog. Untuk SQL Database, kolab selalu SQL_Latin1_General_CP1_CI_AS. Untuk SQL Server database yang terkandung sebagian, kolasenya selalu Latin1_General_100_CI_AS_KS_WS_SC. Jika kolom dikombinasikan dengan kolom lain, maka klausa kolaate (COLLATE DATABASE_DEFAULT) diperlukan untuk menghindari konflik.
Contoh PIVOT Dasar
Contoh kode berikut menghasilkan tabel dua kolom yang memiliki empat baris.
USE AdventureWorks2014 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
Berikut adalah hasil yang ditetapkan.
DaysToManufacture AverageCost
----------------- -----------
0 5.0885
1 223.88
2 359.1082
4 949.4105
Tidak ada produk yang didefinisikan dengan tiga DaysToManufacture.
Kode berikut menampilkan hasil yang sama, dipivot sehingga DaysToManufacture nilai menjadi judul kolom. Kolom disediakan selama tiga [3] hari, meskipun hasilnya adalah NULL.
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(
SELECT DaysToManufacture, StandardCost
FROM Production.Product
) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
Berikut adalah hasil yang ditetapkan.
Cost_Sorted_By_Production_Days 0 1 2 3 4
------------------------------ ----------- ----------- ----------- ----------- -----------
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
Contoh PIVOT kompleks
Skenario umum di mana PIVOT dapat berguna adalah ketika Anda ingin membuat laporan tabulasi silang untuk memberikan ringkasan data. Misalnya, Anda ingin mengkueri PurchaseOrderHeader tabel dalam AdventureWorks2014 database sampel untuk menentukan jumlah pesanan pembelian yang dilakukan oleh karyawan tertentu. Kueri berikut ini menyediakan laporan ini, diurutkan berdasarkan vendor.
USE AdventureWorks2014;
GO
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;
Berikut adalah tataan hasil parsial.
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
----------- ----------- ----------- ----------- ----------- -----------
1492 2 5 4 4 4
1494 2 5 4 5 4
1496 2 4 4 5 5
1498 2 5 4 4 4
1500 3 4 4 5 4
Hasil yang dikembalikan oleh pernyataan subpilih ini dipivot pada EmployeeID kolom.
SELECT PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader;
Nilai unik yang EmployeeID dikembalikan oleh kolom menjadi bidang dalam tataan hasil akhir. Dengan demikian, ada kolom untuk setiap EmployeeID angka yang ditentukan dalam klausa pivot: dalam hal ini karyawan 250, , 251, 256, 257dan 260. Kolom PurchaseOrderID berfungsi sebagai kolom nilai, di mana kolom dikembalikan dalam output akhir, yang disebut kolom pengelompokan, dikelompokkan. Dalam hal ini, kolom pengelompokan diagregasi oleh COUNT fungsi. Perhatikan bahwa pesan peringatan muncul yang menunjukkan bahwa nilai null apa pun yang muncul di kolom tidak dipertimbangkan PurchaseOrderID saat menghitung COUNT untuk setiap karyawan.
Penting
Saat fungsi agregat digunakan dengan PIVOT, keberadaan nilai null apa pun di kolom nilai tidak dipertimbangkan saat menghitung agregasi.
Contoh UNPIVOT
UNPIVOT melakukan hampir operasi terbalik dari PIVOT, dengan memutar kolom ke dalam baris. Misalkan tabel yang dihasilkan dalam contoh sebelumnya disimpan dalam database sebagai pvt, dan Anda ingin memutar pengidentifikasi Emp1kolom , , Emp2, Emp3Emp4, dan Emp5 ke dalam nilai baris yang sesuai dengan vendor tertentu. Dengan demikian, Anda harus mengidentifikasi dua kolom tambahan. Kolom yang akan berisi nilai kolom yang Anda putar (Emp1, Emp2,...) akan dipanggil Employee, dan kolom yang akan menyimpan nilai yang saat ini ada di bawah kolom yang diputar akan disebut Orders. Kolom ini sesuai dengan pivot_column dan value_column, masing-masing, dalam definisi SQL Transact. Berikut adalah kuerinya.
-- Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID INT, Emp1 INT, Emp2 INT,
Emp3 INT, Emp4 INT, Emp5 INT);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
-- Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO
Berikut adalah tataan hasil parsial.
VendorID Employee Orders
----------- ----------- ------
1 Emp1 4
1 Emp2 3
1 Emp3 5
1 Emp4 4
1 Emp5 4
2 Emp1 4
2 Emp2 1
2 Emp3 5
2 Emp4 5
2 Emp5 5
...
Perhatikan bahwa UNPIVOT bukan kebalikan yang tepat dari PIVOT. PIVOT melakukan agregasi dan menggabungkan beberapa baris yang mungkin menjadi satu baris dalam output. UNPIVOT tidak mereproduksi hasil ekspresi bernilai tabel asli karena baris telah digabungkan. Selain itu, nilai null dalam input UNPIVOT menghilang dalam output. Ketika nilai menghilang, itu menunjukkan bahwa mungkin ada nilai null asli dalam input sebelum PIVOT operasi.
Sales.vSalesPersonSalesByFiscalYears Tampilan dalam database sampel AdventureWorks2012 menggunakan PIVOT untuk mengembalikan total penjualan untuk setiap tenaga penjualan, untuk setiap tahun fiskal. Untuk membuat skrip tampilan di SQL Server Management Studio, di Object Explorer, temukan tampilan di bawah folder Tampilan untuk database AdventureWorks2012. Klik kanan nama tampilan, lalu pilih Tampilan Skrip sebagai.