DARI - Menggunakan PIVOT dan UNPIVOT
Berlaku untuk:
Titik akhir analitik SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Platform System (PDW) SQL di Microsoft Fabric Warehouse di Microsoft Fabric
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. PIVOT juga menjalankan agregasi di mana nilai tersebut diperlukan pada nilai kolom yang tersisa yang diinginkan dalam output akhir. UNPIVOT melakukan operasi PIVOTyang berlawanan dengan , dengan memutar kolom ekspresi bernilai tabel ke dalam nilai kolom.
Sintaks untuk PIVOT lebih mudah dan lebih mudah dibaca daripada sintaks yang mungkin ditentukan dalam serangkaian SELECT...CASE pernyataan yang kompleks. Untuk deskripsi lengkap sintaks untuk PIVOT, lihat klausa FROM.
Catatan
Penggunaan PIVOT/UNPIVOT berulang dalam satu pernyataan T-SQL dapat berdampak negatif pada performa kueri.
Sampel kode Transact-SQL dalam artikel ini menggunakan AdventureWorks2022 database sampel atau AdventureWorksDW2022 , yang dapat Anda unduh dari halaman beranda Sampel Microsoft SQL Server dan Proyek Komunitas.
Sintaks
Bagian ini meringkas cara menggunakan PIVOT operator dan UNPIVOT .
Sintaks untuk PIVOT operator.
SELECT [ <non-pivoted column> [ AS <column name> ] , ]
...
[ <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 become column headers>
IN ( <first pivoted column>
, <second pivoted column>
, ... <last pivoted column> )
) AS <alias for the pivot table>
[ <optional ORDER BY clause> ]
[ ; ]
Sintaks untuk UNPIVOT operator.
SELECT [ <non-pivoted column> [ AS <column name> ] , ]
...
[ <output column for names of the pivot columns> [ AS <column name> ] , ]
[ <new output column created for values in result of the source query> [ AS <column name> ] ]
FROM
( <SELECT query that produces the data> )
AS <alias for the source query>
UNPIVOT
(
<new output column created for values in result of the source query>
FOR <output column for names of the pivot columns>
IN ( <first pivoted column>
, <second pivoted column>
, ... <last pivoted column> )
)
[ <optional ORDER BY clause> ]
[ ; ]
Keterangan
Pengidentifikasi kolom dalam UNPIVOT klausul mengikuti kolate katalog.
Untuk Azure 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 PIVOT operator gagal jika ada GROUP BY pada output kolom nonpivot oleh PIVOT. Sebagai solusinya, hapus kolom nonpivot dari GROUP BY. Hasil kueri sama, karena klausa ini GROUP BY adalah 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 set hasilnya.
DaysToManufacture AverageCost
------------------ ------------
0 5.0885
1 223.88
2 359.1082
4 949.4105
Tidak ada produk yang didefinisikan dengan nilai 3 untuk 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 CostSortedByProductionDays,
[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 set hasilnya.
CostSortedByProductionDays 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, yaitu karyawan 250, , 251, 256257, dan 260 dalam contoh ini. 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 . 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 berisi nilai kolom yang Anda putar (Emp1, Emp2, dan sebagainya) disebut Employee, dan kolom yang menyimpan nilai yang saat ini ada di bawah kolom yang diputar, 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
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, NULL nilai dalam input UNPIVOT menghilang dalam output. Ketika nilai menghilang, nilai tersebut menunjukkan bahwa mungkin ada nilai asli NULL 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.