SQL Pivot Baris ke Kolom: Panduan Komprehensif

Apa Arti Memutar Baris ke Kolom di SQL?

Pivot dalam SQL mengacu pada transformasi data dari format berbasis baris ke format berbasis kolom. Transformasi ini berguna untuk pelaporan dan analisis data, memungkinkan tampilan data yang lebih terstruktur dan ringkas. Memutar baris menjadi kolom juga memungkinkan pengguna menganalisis dan meringkas data dengan cara yang menyoroti wawasan utama secara lebih jelas.

Pertimbangkan contoh berikut: Saya memiliki tabel dengan transaksi penjualan harian, dan setiap baris mencatat tanggal, nama produk, dan jumlah penjualan.

Dengan memutar tabel ini, saya dapat menyusunnya ulang untuk menampilkan setiap produk sebagai kolom, dengan data penjualan untuk setiap tanggal di bawah kolom yang sesuai. Perhatikan juga bahwa terjadi agregasi.

Secara tradisional, operasi pivot memerlukan kueri SQL yang kompleks dengan agregasi kondisional. Seiring waktu, implementasi SQL telah berkembang, dengan banyak basis data modern kini menyertakan operator PIVOT dan UNPIVOT untuk memungkinkan transformasi yang lebih efisien dan sederhana.

Memahami SQL Pivot Baris ke Kolom

Operasi pivot SQL mentransformasikan data dengan mengubah nilai baris menjadi kolom. Berikut adalah sintaks dan struktur dasar pivot SQL dengan bagian-bagian berikut:

• SELECT: Pernyataan SELECT merujuk pada kolom yang akan dikembalikan dalam tabel pivot SQL.

• Subquery: Subkueri berisi sumber data atau tabel yang akan disertakan dalam tabel pivot SQL.

• PIVOT: Operator PIVOT berisi agregasi dan filter yang akan diterapkan dalam tabel pivot.

-- Select static columns and pivoted columns
SELECT <static columns>, [pivoted columns]
FROM
    (
        -- Subquery defining source data for pivot
        <subquery that defines data>
    ) AS source
PIVOT
(
    -- Aggregate function applied to value column, creating new columns
    <aggregation function>(<value column>)
    FOR <column to pivot> IN ([list of pivoted columns])
) AS pivot_table;

Mari kita lihat contoh langkah demi langkah berikut untuk menunjukkan cara memutar baris menjadi kolom di SQL. Pertimbangkan tabel SalesData di bawah ini.

Contoh tabel yang akan ditransformasikan menggunakan operator SQL PIVOT. Gambar oleh Penulis.

Saya ingin memutar data ini untuk membandingkan penjualan harian setiap produk. Saya akan mulai dengan memilih subkueri yang akan menyusun operator PIVOT.

-- Subquery defining source data for pivot
SELECT Date, Product, Sales
FROM SalesData;

Sekarang, saya akan menggunakan operator PIVOT untuk mengonversi nilai Product menjadi kolom dan mengagregasi Sales menggunakan operator SUM.

-- Select Date and pivoted columns for each product
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- Subquery to fetch Date, Product, and Sales columns
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- Aggregate Sales by Product, pivoting product values to columns
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

Contoh transformasi keluaran menggunakan SQL pivot baris ke kolom. Gambar oleh Penulis.

Meskipun memutar data menyederhanakan ringkasan data, teknik ini memiliki potensi masalah. Berikut adalah tantangan potensial dengan SQL pivot dan cara mengatasinya.

• Nama Kolom Dinamis: Ketika nilai yang akan diputar (misalnya, jenis Produk) tidak diketahui, pengodean keras nama kolom tidak akan berhasil. Beberapa basis data, seperti SQL Server, mendukung SQL dinamis dengan stored procedure untuk menghindari masalah ini, sementara yang lain mengharuskan penanganan di lapisan aplikasi.

• Menangani Nilai NULL: Ketika tidak ada data untuk kolom yang diputar tertentu, hasilnya mungkin menyertakan NULL. Anda dapat menggunakan COALESCE untuk mengganti nilai NULL dengan nol atau placeholder lainnya.

• Kompatibilitas Antar Basis Data: Tidak semua basis data mendukung operator PIVOT secara langsung. Anda dapat mencapai hasil serupa dengan pernyataan CASE dan agregasi kondisional jika dialek SQL Anda tidak mendukungnya.

SQL Pivot Baris ke Kolom: Contoh dan Use Case

Berbagai metode digunakan untuk memutar data di SQL, bergantung pada basis data yang digunakan atau persyaratan lainnya. Meskipun operator PIVOT umum digunakan di SQL Server, teknik lain seperti pernyataan CASE memungkinkan transformasi serupa tanpa dukungan PIVOT langsung. Saya akan membahas dua metode umum memutar data di SQL, serta kelebihan dan kekurangannya.

Menggunakan operator PIVOT

Operator PIVOT, tersedia di SQL Server, menyediakan cara yang lugas untuk memutar baris menjadi kolom dengan menentukan fungsi agregasi dan mendefinisikan kolom yang akan diputar.

Pertimbangkan tabel berikut bernama sales_data.

Contoh tabel Orders untuk ditransformasikan menggunakan operator PIVOT. Gambar oleh Penulis.

Saya akan menggunakan operator PIVOT untuk mengagregasi data sehingga total sales_revenue setiap tahun ditampilkan dalam kolom.

-- Use PIVOT to aggregate sales revenue by year
SELECT *
FROM (
    -- Select the relevant columns from the source table
    SELECT sale_year, sales_revenue
    FROM sales_data
) AS src
PIVOT (
    -- Aggregate sales revenue for each year
    SUM(sales_revenue)
    -- Create columns for each year
    FOR sale_year IN ([2020], [2021], [2022], [2023])
) AS piv;

Contoh transformasi keluaran menggunakan SQL PIVOT. Gambar oleh Penulis.

Menggunakan operator PIVOT memiliki kelebihan dan keterbatasan berikut:

• Kelebihan: Metode ini efisien ketika kolom diindeks dengan baik. Sintaksnya juga sederhana dan lebih mudah dibaca.

• Keterbatasan: Tidak semua basis data mendukung operator PIVOT. Ini mengharuskan kolom ditentukan terlebih dahulu, dan pivot dinamis memerlukan kompleksitas tambahan.

Pivot manual dengan pernyataan CASE

Anda juga dapat menggunakan pernyataan CASE untuk memutar data secara manual di basis data yang tidak mendukung operator PIVOT, seperti MySQL dan PostgreSQL. Pendekatan ini menggunakan agregasi kondisional dengan mengevaluasi setiap baris dan secara kondisional menetapkan nilai ke kolom baru berdasarkan kriteria tertentu.

Sebagai contoh, kita dapat memutar data secara manual di tabel sales_data yang sama dengan pernyataan CASE.

-- Aggregate sales revenue by year using CASE statements
SELECT 
    -- Calculate total sales revenue for each year
    SUM(CASE WHEN sale_year = 2020 THEN sales_revenue ELSE 0 END) AS sales_2020,
    SUM(CASE WHEN sale_year = 2021 THEN sales_revenue ELSE 0 END) AS sales_2021,
    SUM(CASE WHEN sale_year = 2022 THEN sales_revenue ELSE 0 END) AS sales_2022,
    SUM(CASE WHEN sale_year = 2023 THEN sales_revenue ELSE 0 END) AS sales_2023
FROM 
    sales_data;

Contoh transformasi keluaran menggunakan pernyataan SQL CASE. Gambar oleh Penulis.

Menggunakan pernyataan CASE untuk transformasi memiliki kelebihan dan keterbatasan berikut:

• Kelebihan: Metode ini berfungsi di semua basis data SQL dan fleksibel untuk menghasilkan kolom baru secara dinamis, bahkan ketika nama produk tidak diketahui atau sering berubah.

• Keterbatasan: Kueri dapat menjadi kompleks dan panjang jika ada banyak kolom yang diputar. Karena banyaknya pemeriksaan kondisional, metode ini sedikit lebih lambat dibandingkan operator PIVOT.

Pertimbangan Kinerja Saat Memutar Baris ke Kolom

Memutar baris ke kolom di SQL dapat berdampak pada kinerja, terutama saat bekerja dengan dataset besar. Berikut beberapa kiat dan praktik terbaik untuk membantu Anda menulis kueri pivot yang efisien, mengoptimalkan kinerjanya, dan menghindari jebakan umum.

Praktik terbaik

Berikut adalah praktik terbaik untuk mengoptimalkan kueri Anda dan meningkatkan kinerja.

• Strategi Pengindeksan: Pengindeksan yang tepat sangat penting untuk mengoptimalkan kueri pivot, memungkinkan SQL mengambil dan memproses data lebih cepat. Selalu indeks kolom yang sering digunakan dalam klausa WHERE atau kolom yang Anda kelompokkan untuk mengurangi waktu pemindaian.

• Hindari Pivot Bersarang: Menumpuk beberapa operasi pivot dalam satu kueri dapat sulit dibaca dan lebih lambat dieksekusi. Sederhanakan dengan memecah kueri menjadi beberapa bagian atau menggunakan tabel sementara.

• Batasi Kolom dan Baris dalam Pivot: Hanya putar kolom yang diperlukan untuk analisis karena memutar banyak kolom dapat menghabiskan sumber daya dan membuat tabel besar.

Menghindari jebakan umum

Berikut kesalahan umum yang mungkin Anda temui dalam kueri pivot dan cara menghindarinya.

• Pemindaian Tabel Penuh yang Tidak Perlu: Kueri pivot dapat memicu pemindaian tabel penuh, terutama jika tidak ada indeks yang relevan. Hindari pemindaian tabel penuh dengan mengindeks kolom kunci dan memfilter data sebelum menerapkan pivot.

• Menggunakan SQL Dinamis untuk Pivot yang Sering: Menggunakan SQL dinamis dapat memperlambat kinerja karena kompilasi ulang kueri. Untuk menghindari masalah ini, cache atau batasi pivot dinamis pada skenario tertentu dan pertimbangkan menangani kolom dinamis di lapisan aplikasi bila memungkinkan.

• Agregasi pada Dataset Besar Tanpa Pra-pemfilteran: Fungsi agregasi seperti SUM atau COUNT pada dataset besar dapat memperlambat kinerja basis data. Alih-alih memutar seluruh dataset, filter data terlebih dahulu menggunakan klausa WHERE.

• Nilai NULL pada Kolom yang Diputar: Operasi pivot sering menghasilkan nilai NULL ketika tidak ada data untuk kolom tertentu. Ini dapat memperlambat kueri dan membuat hasil lebih sulit ditafsirkan. Untuk menghindari masalah ini, gunakan fungsi seperti COALESCE untuk mengganti nilai NULL dengan default.

• Pengujian Hanya dengan Data Sampel: Kueri pivot dapat berperilaku berbeda pada dataset besar karena peningkatan kebutuhan memori dan pemrosesan. Selalu uji kueri pivot pada data nyata atau sampel yang representatif untuk menilai dampak kinerja secara akurat.

Coba jalur karier SQL Server Developer kami, yang mencakup segala hal mulai dari transaksi dan penanganan error hingga peningkatan kinerja kueri.

Implementasi Spesifik Basis Data

Operasi pivot sangat berbeda di antara basis data seperti SQL Server, MySQL, dan Oracle. Masing-masing basis data ini memiliki sintaks dan keterbatasan spesifik. Saya akan membahas contoh memutar data di berbagai basis data beserta fitur utamanya.

SQL Server

SQL Server menyediakan operator PIVOT bawaan, yang mudah digunakan saat memutar baris menjadi kolom. Operator PIVOT mudah digunakan dan terintegrasi dengan fungsi agregasi SQL Server yang kuat. Fitur utama pivot di SQL mencakup hal-hal berikut:

• Dukungan Langsung untuk PIVOT dan UNPIVOT: Operator PIVOT SQL Server memungkinkan transformasi baris-ke-kolom dengan cepat. Operator UNPIVOT juga dapat membalikkan proses ini.

• Opsi Agregasi: Operator PIVOT memungkinkan berbagai fungsi agregasi, seperti SUM, COUNT, dan AVG.

Keterbatasan operator PIVOT di SQL Server adalah nilai kolom yang akan diputar harus diketahui sebelumnya, sehingga kurang fleksibel untuk data yang berubah secara dinamis.

Pada contoh di bawah, operator PIVOT mengonversi nilai Product menjadi kolom dan mengagregasi Sales menggunakan operator SUM.

-- Select Date and pivoted columns for each product
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- Subquery to fetch Date, Product, and Sales columns
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- Aggregate Sales by Product, pivoting product values to columns
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

Saya merekomendasikan mengikuti kursus Introduction to SQL Server dari DataCamp untuk menguasai dasar-dasar SQL Server guna analisis data.

MySQL

MySQL tidak memiliki dukungan native untuk operator PIVOT. Namun, Anda dapat menggunakan pernyataan CASE untuk memutar baris menjadi kolom secara manual dan mengombinasikannya dengan fungsi agregasi lain seperti SUM, AVG, dan COUNT. Meskipun metode ini fleksibel, metodenya bisa menjadi kompleks jika Anda memiliki banyak kolom untuk diputar.

Kueri di bawah ini menghasilkan keluaran yang sama seperti contoh PIVOT SQL Server dengan mengagregasi penjualan setiap produk secara kondisional menggunakan pernyataan CASE.

-- Select Date and pivoted columns for each product
SELECT 
    Date,
    -- Use CASE to create a column for Laptop and Mouse sales
    SUM(CASE WHEN Product = 'Laptop' THEN Sales ELSE 0 END) AS Laptop,
    SUM(CASE WHEN Product = 'Mouse' THEN Sales ELSE 0 END) AS Mouse
FROM SalesData
GROUP BY Date;

Oracle

Oracle mendukung operator PIVOT, yang memungkinkan transformasi baris menjadi kolom secara langsung. Sama seperti SQL Server, Anda perlu menentukan kolom transformasi secara eksplisit.

Dalam kueri di bawah, operator PIVOT mengonversi nilai ProductName menjadi kolom dan mengagregasi SalesAmount menggunakan operator SUM.

SELECT *
FROM (
    -- Source data selection
    SELECT SaleDate, ProductName, SaleAmount FROM SalesData
)
PIVOT (
    -- Aggregate Sales by Product, creating pivoted columns
    SUM(SaleAmount)
    FOR ProductName IN ('Laptop' AS Laptop, 'Mouse' AS Mouse)
);

Contoh transformasi keluaran menggunakan operator SQL PIVOT di Oracle. Gambar oleh Penulis.

Teknik Lanjutan untuk Memutar Baris ke Kolom di SQL

Teknik lanjutan untuk memutar baris menjadi kolom berguna ketika Anda memerlukan fleksibilitas dalam menangani data yang kompleks. Teknik dinamis dan penanganan beberapa kolom sekaligus memungkinkan Anda mentransformasikan data dalam skenario di mana pivot statis terbatas. Mari kita jelajahi kedua metode ini secara mendalam.

Pivot dinamis

Pivot dinamis memungkinkan Anda membuat kueri pivot yang secara otomatis menyesuaikan dengan perubahan data. Teknik ini sangat berguna ketika Anda memiliki kolom yang sering berubah, seperti nama produk atau kategori, dan Anda ingin kueri secara otomatis menyertakan entri baru tanpa memperbaruinya secara manual.

Misalkan kita memiliki tabel SalesData dan dapat membuat pivot dinamis yang menyesuaikan jika produk baru ditambahkan. Pada kueri di bawah, @columns secara dinamis membangun daftar kolom yang diputar, dan sp_executesql menjalankan SQL yang dihasilkan.

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
-- Step 1: Generate a list of distinct products to pivot
SELECT @columns = STRING_AGG(QUOTENAME(Product), ', ') 
FROM (SELECT DISTINCT Product FROM SalesData) AS products;
-- Step 2: Build the dynamic SQL query
SET @sql = N'
SELECT Date, ' + @columns + '
FROM 
    (SELECT Date, Product, Sales FROM SalesData) AS source
PIVOT
(
    SUM(Sales)
    FOR Product IN (' + @columns + ')
) AS pivot_table;';
-- Step 3: Execute the dynamic SQL
EXEC sp_executesql @sql;

Menangani banyak kolom

Dalam skenario di mana Anda perlu memutar beberapa kolom sekaligus, Anda akan menggunakan operator PIVOT dan teknik agregasi tambahan untuk membuat beberapa kolom dalam kueri yang sama. 

Pada contoh di bawah, saya telah memutar kolom Sales dan Quantity berdasarkan Product.

-- Pivot Sales and Quantity for Laptop and Mouse by Date
SELECT 
    p1.Date, 
    p1.[Laptop] AS Laptop_Sales, 
    p2.[Laptop] AS Laptop_Quantity, 
    p1.[Mouse] AS Mouse_Sales, 
    p2.[Mouse] AS Mouse_Quantity
FROM 
    (
        -- Pivot for Sales
        SELECT Date, [Laptop], [Mouse]
        FROM 
            (SELECT Date, Product, Sales FROM SalesData) AS source
        PIVOT
            (SUM(Sales) FOR Product IN ([Laptop], [Mouse])) AS pivot_sales
    ) p1
JOIN
    (
        -- Pivot for Quantity
        SELECT Date, [Laptop], [Mouse]
        FROM 
            (SELECT Date, Product, Quantity FROM SalesData) AS source
        PIVOT
            (SUM(Quantity) FOR Product IN ([Laptop], [Mouse])) AS pivot_quantity
    ) p2
ON p1.Date = p2.Date;

Contoh transformasi keluaran beberapa kolom menggunakan operator SQL PIVOT. Gambar oleh Penulis.

Memutar banyak kolom memungkinkan laporan yang lebih detail dengan memutar beberapa atribut per item, sehingga menghasilkan wawasan yang lebih kaya. Namun, sintaksnya bisa kompleks, terutama jika terdapat banyak kolom. Pengodean keras mungkin diperlukan kecuali dikombinasikan dengan teknik pivot dinamis, yang menambah kompleksitas lebih lanjut.

Kesimpulan

Memutar baris ke kolom adalah teknik SQL yang layak dipelajari. Saya telah melihat teknik pivot SQL digunakan untuk membuat tabel retensi kohort, di mana Anda dapat melacak retensi pengguna dari waktu ke waktu. Saya juga melihat teknik pivot SQL digunakan saat menganalisis data survei, di mana setiap baris mewakili responden, dan setiap pertanyaan dapat diputar menjadi kolomnya. 

Kursus Reporting in SQL kami adalah pilihan yang tepat jika Anda ingin mempelajari lebih lanjut tentang meringkas dan menyiapkan data untuk presentasi dan/atau pembuatan dasbor. Jalur karier Associate Data Analyst in SQL dan Associate Data Engineer in SQL kami juga merupakan ide bagus, dan menambah nilai pada resume apa pun, jadi daftar sekarang.