DARI - Menggunakan PIVOT dan UNPIVOT

Artikel
08/11/2023

Berlaku untuk:SQL Server Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics Analytics Platform System (PDW)

Anda dapat menggunakan PIVOT operator relasional 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 mereka 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 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, kolase selalu SQL_Latin1_General_CP1_CI_AS. Untuk database yang sebagian berisi SQL Server, 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.

Di kumpulan Microsoft Fabric dan Azure Synapse Analytics, kueri dengan operator PIVOT gagal jika ada GROUP BY pada output kolom nonpivot oleh PIVOT. Sebagai solusinya, hapus kolom nonpivot dari GROUP BY. Hasil kueri sama, karena klausa GROUP BY ini duplikat.

Contoh PIVOT Dasar

Contoh kode berikut menghasilkan tabel dua kolom yang memiliki empat baris.

USE AdventureWorks2022;  
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 AdventureWorks2022 database sampel untuk menentukan jumlah pesanan pembelian yang ditempatkan oleh karyawan tertentu. Kueri berikut menyediakan laporan ini, diurutkan menurut vendor.

USE AdventureWorks2022;  
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 hasil parsial yang ditetapkan.

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, tempat kolom dikembalikan dalam output akhir, yang disebut kolom pengelompokan, dikelompokkan. Dalam hal ini, kolom pengelompokan dikumpulkan 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, Emp3, Emp4dan 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 Transact-SQL. 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 hasil parsial yang ditetapkan.

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 kemungkinan beberapa baris ke dalam 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, nilai menunjukkan bahwa mungkin ada nilai null asli dalam input sebelum PIVOT operasi.

Sales.vSalesPersonSalesByFiscalYears Tampilan dalam AdventureWorks2022 database sampel 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 AdventureWorks2022 database. Klik kanan nama tampilan, lalu pilih Tampilan Skrip sebagai.