SQL PIVOT Nasıl Kullanılır

Kısa Cevap: SQL’de Nasıl Pivot Yapılır

SQL Server’daki PIVOT operatörü, satırları sütunlara dönüştürmeye olanak tanıdığı için verileri özetlerken kullanışlıdır. Aşağıda, bir ürünün ABD’nin beş büyük şehrindeki genel satışlarını gösteren city_sales tablosunu ele alalım.

SQL PIVOT kullanarak dönüştürülecek tablo örneği. Görsel: Yazar.

Yukarıdaki tabloda birden çok sütunu pivotlamak için PIVOT operatörünü kullanan aşağıdaki sorguyu kullanacağız.

-- 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;

SQL PIVOT kullanılarak yapılan çıktı dönüşümü örneği. Görsel: Yazar.

SQL’de PIVOT Nedir

Pivotlama, tablo verilerinde satırları sütunlara dönüştürmek için kullanılan bir SQL tekniğidir. SQL Server ve Oracle’da pivotlama, PIVOT operatörü ile yapılır. Aşağıda gösterilen SQL PIVOT operatörü söz dizimi üç ana bölümden oluşur:

• SELECT: SELECT ifadesi, SQL pivot tablosunda döndürülecek sütunlara referans verir.

• Alt sorgu: Alt sorgu, SQL pivot tablosuna dahil edilecek veri kaynağını veya tabloyu içerir.

• PIVOT: PIVOT operatörü, pivot tabloda uygulanacak özetlemeleri ve filtreyi barındırır.

-- 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

SQL PIVOT’un Veritabanına Özgü Uygulamaları

SQL Server ve Oracle veritabanları PIVOT operatörünü doğrudan destekler. Ancak, MySQL ve PostgreSQL’de SQL’de pivot tablo oluşturmak için alternatif yöntemler vardır.

SQL Server’da PIVOT

SQL Server, PIVOT operatörünü yerel olarak destekler. Burada, satırları sütunlara dönüştürmek ve SUM() gibi toplama fonksiyonlarıyla verileri özetlemek için PIVOT operatörünü kullanacağız. Ayrıca WHERE, GROUP BY ve ORDER BY gibi SQL ifadelerini de daha rafine veri işlemleri için kullanacağız.

Aşağıdaki örnek, 2020 ve sonrasına ait verileri filtrelemek (WHERE), verileri şehir ve yıla göre gruplamak (GROUP BY) ve verileri şehre göre sıralamak (ORDER BY) için PIVOT operatörünün nasıl kullanılacağını göstermektedir:

-- 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;         

Yaygın ifadelerle SQL PIVOT kullanılarak dönüştürülen tablodan örnek çıktı. Görsel: Yazar.

Oracle’da PIVOT

SQL Server’a benzer şekilde, Oracle da satırları sütunlara dönüştürmek için PIVOT operatörünü kullanır. Ancak Oracle veritabanındaki PIVOT operatörünün söz dizimi SQL Server’dakinden biraz farklıdır. Aşağıdaki sorgu, PIVOT operatörünün Oracle’da nasıl göründüğünü gösterir. SQL Server’daki dış SELECT ifadesinin aksine, sütunların PIVOT operatörü içinde takma adlarla verildiğine dikkat edin.

-- 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;

MySQL’de Pivotlama

MySQL veritabanı SQL PIVOT operatörünü desteklemez. MySQL’de SQL pivot tablolar oluşturmak için koşullu özetleme ile CASE ifadesini kullanmanız gerekir. Örneğin, aşağıdaki sorgu, city’ye göre gruplanıp sıralanmış, farklı yıllar için satış toplamlarına göre verileri özetleyen bir pivot tablo oluşturacaktır.

-- 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; 

PostgreSQL’de Pivotlama

PostgreSQL veritabanı da SQL PIVOT operatörünü desteklemez. Bu nedenle, pivot tablolar oluştururken koşullu özetleme ile CASE ifadesini kullanmak önemlidir. Aşağıdaki sorgu, PostgreSQL’de pivot tablolar oluşturmak için kullanılan koşullu CASE ifadelerine bir örnektir.

-- 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; 

SQL PIVOT ile İleri Teknikler

Karmaşık sorgular yazmak için bazı ileri düzey SQL pivotlama teknikleri vardır. Bu bölümde, hangi sütunların pivotlanacağının bilinmediği pivot tablolar için sorgular oluşturmakta kullandığımız dinamik pivotlamaya bakacağız. Bu yöntem, pivot tabloyu çalışma zamanında oluşturmak için SQL kullanır.

SQL Server’da Dinamik PIVOT

Aşağıdaki sorgu, SQL Server’da year sütununu dinamik olarak pivotlamak için PIVOT kullanır. Sorgu, city_sales tablosundan benzersiz yılları alacaktır. Ardından, alınan yılları kullanarak dinamik bir PIVOT sorgusu oluşturup çalıştıracaktır.

-- 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;

SQL dinamik PIVOT kullanılarak oluşturulmuş tablonun örnek çıktısı. Görsel: Yazar.

Oracle’da Dinamik PIVOT

Oracle veritabanında dinamik pivotlama, EXECUTE IMMEDIATE ifadesi kullanılarak dinamik sorgunun yürütülmesiyle desteklenir. LISTAGG fonksiyonu da pivot içinde takma adlarda kullanılan tek tırnakları ' ' ve sütun adlarını dinamik olarak birleştirmek için kullanılır.

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;

MySQL’de Dinamik Pivotlama

MySQL, doğrudan dinamik SQL’i desteklemez. Bu nedenle MySQL’de dinamik PIVOT için bir saklı yordam (stored procedure) oluşturmanız gerekir. Aşağıdaki sorgu, dinamik bir PIVOT sorgusu oluşturmak için saklı yordamın nasıl kullanılacağını gösterir.

-- 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 ;

Saklı yordamı oluşturduktan sonra, dinamik PIVOT sorgusunu çalıştırmak için saklı yordamı çağırmanız gerekir:

CALL dynamic_pivot();

PostgreSQL’de Dinamik Pivotlama

Benzer şekilde, aşağıdaki sorguyu kullanarak PostgreSQL’de dinamik bir PIVOT oluşturabilirsiniz:

-- 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
$;

Sonuç ve İleri Öğrenme

SQL’de PIVOT kullanmayı anlamak, verileri verimli şekilde dönüştürmek ve analiz etmek istiyorsanız önemlidir. SQL’de pivot tablolar oluştururken, farklı veritabanlarında PIVOT operatörünün farklı uygulamalarını öğrenmek kritik önemdedir. Bir veri analisti olarak, farklı veri kümelerini analiz etmek için PIVOT’u nasıl ve ne zaman uygulayacağınızı öğrenmek üzere SQL becerilerinizi geliştirmeye devam etmenizi öneririm.

İster sektöre adım atmak isteyen bir veri analisti adayı olun ister daha deneyimli bir analist, veri analizi becerilerinizi geliştirmek için DataCamp’in Introduction to SQL ve Intermediate SQL kurslarını almanızı öneririm. Ayrıca, bu derste ele alınan alt sorgular ve diğer kavramları öğreten Data Manipulation in SQL kursumuzla birlikte, özellikle SQL Server’ı kapsayan Introduction to SQL Server kursumuzu da tavsiye ederim.