Cara Menggunakan SQL PIVOT

Jawaban Singkat: Cara Melakukan Pivot di SQL

Operator PIVOT di SQL Server berguna saat meringkas data karena memungkinkan transformasi baris menjadi kolom. Perhatikan tabel city_sales di bawah ini, yang menunjukkan penjualan umum suatu produk di lima kota besar AS.

Contoh tabel untuk ditransformasikan menggunakan SQL PIVOT. Gambar oleh Penulis.

Kita akan menggunakan kueri berikut, yang memakai operator PIVOT, untuk memutar beberapa kolom pada tabel di atas.

-- Select the columns for the output: city and sales data for 2019, 2020, and 2021
SELECT 
    city,
    [2019] AS Sales_2019,
    [2020] AS Sales_2020,
    [2021] AS Sales_2021
FROM
    (
        -- Subquery to select city, year, and sales from city_sales table
        SELECT city, year, sales 
        FROM city_sales
    ) AS src
PIVOT
(
    -- Pivot the sales data to have years as columns and sum the sales for each year
    SUM(sales)
    FOR year IN ([2019], [2020], [2021])
) AS pvt;

Contoh keluaran transformasi menggunakan SQL PIVOT. Gambar oleh Penulis.

Apa itu PIVOT dalam SQL

Pivot adalah teknik dalam SQL yang digunakan untuk mengubah baris menjadi kolom pada data tabel. Di SQL Server dan Oracle, pivot dilakukan dengan operator PIVOT. Sintaks operator SQL PIVOT, seperti ditunjukkan di bawah, memiliki tiga bagian utama:

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

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

• PIVOT: Operator PIVOT berisi agregasi dan filter yang akan diterapkan pada pivot table.

-- Select the non-pivoted column and the pivoted columns with aliases
SELECT 
    [non-pivoted column],               
    [first pivoted column] AS [column name],   
    [second pivoted column] AS [column name],  
    ...
FROM
    (
        -- Subquery to select the necessary columns from the source table
        SELECT [columns] 
        FROM [source_table]
    ) AS source_table
PIVOT
(
    -- Pivot operation to aggregate data and transform rows into columns
    [aggregate_function]([pivot_column])        
    FOR [pivot_column] IN ([first pivoted column], [second pivoted column], ...) 
) AS pivot_table; -- Alias for the result of the pivot operation

Implementasi Spesifik Basis Data untuk SQL PIVOT

Basis data SQL Server dan Oracle mendukung langsung operator PIVOT. Namun, MySQL dan PostgreSQL memiliki metode alternatif untuk membuat pivot table dalam SQL.

PIVOT di SQL Server

SQL Server menyediakan dukungan native untuk operator PIVOT. Di sini, kita akan menggunakan operator PIVOT untuk mengubah baris menjadi kolom dan meringkas data menggunakan fungsi agregat seperti SUM(). Kita juga akan menggunakan klausa SQL seperti WHERE, GROUP BY, dan ORDER BY untuk manipulasi data yang lebih terarah.

Contoh di bawah menunjukkan cara menggunakan operator PIVOT untuk memfilter data untuk tahun 2020 atau lebih baru (WHERE), mengelompokkan data berdasarkan kota dan tahun (GROUP BY), serta mengurutkan data berdasarkan kota (ORDER BY):

-- Select the city and sales data for the years 2019, 2020, and 2021
SELECT 
    city,                         
    [2019] AS Sales_2019,             
    [2020] AS Sales_2020,           
    [2021] AS Sales_2021              
FROM
    (
        -- Subquery to select city, year, and sales from the city_sales table
        SELECT city, year, sales 
        FROM city_sales
        WHERE year >= 2020 -- filtering
        GROUP BY city, year, sales -- grouping
    ) AS src                   
PIVOT
(
    -- Pivot the sales data to have years as columns, averaging the sales over each year
    SUM(sales) -- aggregating
    FOR year IN ([2019], [2020], [2021]) 
) AS pvt;         

Contoh keluaran tabel yang ditransformasikan menggunakan SQL PIVOT dengan klausa umum. Gambar oleh Penulis.

PIVOT di Oracle

Mirip dengan SQL Server, Oracle juga menggunakan operator PIVOT untuk mengubah baris menjadi kolom. Namun, sintaks operator PIVOT di database Oracle sedikit berbeda dari SQL Server. Kueri di bawah menunjukkan bagaimana operator PIVOT muncul di Oracle. Perhatikan bahwa kolom diberi alias di dalam operator PIVOT, tidak seperti pernyataan SELECT luar pada SQL Server.

-- Outer SELECT to choose all columns resulting from the PIVOT operation
SELECT *
FROM (
    -- Inner SELECT to retrieve the raw data of city, year, and sales
    SELECT city, year, sales
    FROM sales
)
-- PIVOT operation to convert rows to columns
PIVOT (
    SUM(sales) 
    -- Specify the year values to pivot and alias them as Sales_<year>
    FOR year IN (2019 AS Sales_2019, 2020 AS Sales_2020, 2021 AS Sales_2021)
)
ORDER BY city;

Pivot di MySQL

Basis data MySQL tidak mendukung operator SQL PIVOT. Untuk membuat pivot table di MySQL, Anda harus menggunakan pernyataan CASE dengan agregasi bersyarat. Sebagai contoh, kueri di bawah akan membuat pivot table untuk mengagregasikan data berdasarkan jumlah penjualan untuk berbagai tahun, dikelompokkan dan diurutkan menurut city.

-- Select the city and sum the sales data for the years 2019, 2020, and 2021
SELECT 
    city,
    SUM(CASE WHEN year = 2019 THEN sales ELSE 0 END) AS Sales_2019,
    SUM(CASE WHEN year = 2020 THEN sales ELSE 0 END) AS Sales_2020,
    SUM(CASE WHEN year = 2021 THEN sales ELSE 0 END) AS Sales_2021
FROM 
    city_sales 
GROUP BY 
    city
ORDER BY 
    city; 

Pivot di PostgreSQL

Basis data PostgreSQL juga tidak mendukung operator SQL PIVOT. Oleh karena itu, saat membuat pivot table, penting untuk menggunakan pernyataan CASE dengan agregasi bersyarat. Kueri di bawah adalah contoh pernyataan CASE bersyarat yang digunakan untuk membuat pivot table di PostgreSQL.

-- Select the city and sum the sales data for the years 2019, 2020, and 2021
SELECT 
    city,
    SUM(CASE WHEN year = 2019 THEN sales ELSE 0 END) AS Sales_2019, 
    SUM(CASE WHEN year = 2020 THEN sales ELSE 0 END) AS Sales_2020,
    SUM(CASE WHEN year = 2021 THEN sales ELSE 0 END) AS Sales_2021
FROM 
    city_sales
GROUP BY 
    city
ORDER BY 
    city; 

Teknik Lanjutan dengan SQL PIVOT

Ada beberapa teknik pivot SQL tingkat lanjut untuk menulis kueri yang kompleks. Pada bagian ini, kita akan melihat dynamic pivoting, yang kita gunakan untuk membuat kueri pivot table saat kolom yang akan diputar belum diketahui. Metode ini menggunakan SQL untuk membuat pivot table saat runtime.

Dynamic PIVOT di SQL Server

Kueri di bawah menggunakan PIVOT untuk secara dinamis memutar kolom year di SQL Server. Kueri ini akan mengambil tahun-tahun unik dari tabel city_sales. Lalu akan membangun dan mengeksekusi kueri PIVOT dinamis menggunakan tahun-tahun yang diambil.

-- Declare variables to hold the column names and the dynamic query
DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);
-- Get distinct values of the year column and concatenate them into a string
SELECT @cols = STRING_AGG(QUOTENAME(year), ',') 
               FROM (SELECT DISTINCT year FROM city_sales) AS years;
-- Construct the dynamic PIVOT query
SET @query = '
SELECT city, ' + @cols + '
FROM
(
    -- Subquery to select city, year, and sales from the city_sales table
    SELECT city, year, sales
    FROM city_sales
) AS src
PIVOT
(
    -- Pivot the sales data to have years as columns, summing the sales for each year
    SUM(sales)
    FOR year IN (' + @cols + ')
) AS pvt
ORDER BY city';  -- Order the results by city
-- Execute the dynamic PIVOT query
EXEC sp_executesql @query;

Contoh keluaran tabel menggunakan SQL dynamic PIVOT. Gambar oleh Penulis.

Dynamic PIVOT di Oracle

Di database Oracle, dynamic pivoting didukung dengan mengeksekusi kueri dinamis menggunakan pernyataan EXECUTE IMMEDIATE. Fungsi LISTAGG juga digunakan untuk secara dinamis mengagregasikan nama kolom dan tanda kutip tunggal ' ' yang digunakan dalam alias di dalam pivot.

DECLARE
    cols VARCHAR2(4000);
    sql_query VARCHAR2(4000);
BEGIN
    -- Get the list of years dynamically
    SELECT LISTAGG('''' || year || ''' AS ' || 'sales_' || year, ',') 
    INTO cols
    FROM (SELECT DISTINCT year FROM city_sales);
    -- Construct the dynamic SQL query
    sql_query := 'SELECT * FROM (
                      SELECT city, year, sales
                      FROM city_sales
                  )
                  PIVOT (
                      SUM(sales)
                      FOR year IN (' || cols || ')
                  )
                  ORDER BY city';
    -- Execute the dynamic SQL query
    EXECUTE IMMEDIATE sql_query;
END;

Dynamic Pivot di MySQL

MySQL tidak mendukung SQL dinamis secara langsung. Karena itu, Anda harus membuat stored procedure untuk PIVOT dinamis di MySQL. Kueri di bawah menunjukkan cara menggunakan stored procedure untuk membuat kueri PIVOT dinamis.

-- Declare variables to hold the dynamic columns (cols) and the final SQL query
DELIMITER $
CREATE PROCEDURE dynamic_pivot()
BEGIN
    DECLARE cols VARCHAR(1000);
    DECLARE sql_query VARCHAR(2000);
    -- Get the list of distinct years
    SELECT GROUP_CONCAT(DISTINCT
        CONCAT('SUM(CASE WHEN year = ', year, ' THEN sales ELSE 0 END) AS ', year, '')
    ) INTO cols
    FROM city_sales;
    -- Construct the dynamic SQL query
    SET sql_query = CONCAT('SELECT city, ', cols, ' FROM city_sales GROUP BY city ORDER BY city');
    -- Prepare and execute the SQL query
    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $
DELIMITER ;

Setelah membuat stored procedure, Anda harus memanggil stored procedure tersebut untuk mengeksekusi kueri PIVOT dinamis:

CALL dynamic_pivot();

Dynamic Pivot di PostgreSQL

Serupa, Anda dapat membuat PIVOT dinamis di PostgreSQL menggunakan kueri berikut:

-- Block declaration to execute PL/pgSQL code in an anonymous code block
DO
$
DECLARE
    cols text;  -- Variable to store the list of columns for the dynamic query
    query text; -- Variable to store the dynamic SQL query
BEGIN
    -- Get distinct years and construct the list of SUM(CASE...) statements
    SELECT STRING_AGG(DISTINCT 'SUM(CASE WHEN year = ' || year || ' THEN sales ELSE 0 END) AS "Sales_' || year || '"', ', ')
    INTO cols
    FROM city_sales;
    -- Construct the dynamic PIVOT query
    query := 'SELECT city, ' || cols || ' FROM city_sales GROUP BY city ORDER BY city';
    -- Execute the dynamic PIVOT query
    EXECUTE query;
END
$;

Kesimpulan dan Pembelajaran Lanjutan

Memahami cara menggunakan PIVOT dalam SQL penting jika Anda ingin mentransformasi dan menganalisis data secara efisien. Saat membuat pivot table di SQL, mempelajari beragam implementasi operator PIVOT pada berbagai basis data sangat krusial. Sebagai analis data, saya mendorong Anda untuk terus berlatih keterampilan SQL guna mempelajari bagaimana dan kapan menerapkan PIVOT untuk menganalisis berbagai dataset.

Jika Anda seorang calon analis data yang ingin menapaki industri ini atau analis berpengalaman, saya merekomendasikan untuk mengambil kursus DataCamp Introduction to SQL dan Intermediate SQL untuk meningkatkan keterampilan analisis data Anda. Saya juga merekomendasikan mengambil kursus Data Manipulation in SQL, yang mengajarkan subquery dan konsep lain yang dibahas dalam tutorial ini, bersama dengan kursus Introduction to SQL Server, yang membahas SQL Server secara khusus.