SQL PIVOT gebruiken

Het snelle antwoord: zo pivot je in SQL

De PIVOT-operator in SQL Server is handig voor het samenvatten van data, omdat je er rijen mee kunt omzetten in kolommen. Bekijk de onderstaande city_sales-tabel, die de algemene verkoop van een product in vijf grote Amerikaanse steden laat zien.

Voorbeeld van outputtransformatie met SQL PIVOT. Afbeelding door de auteur.

We gebruiken de volgende query, die de PIVOT-operator toepast, om meerdere kolommen in de bovenstaande tabel te pivoteren.

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

Voorbeeld van outputtransformatie met SQL PIVOT. Afbeelding door de auteur.

Wat is PIVOT in SQL

Pivoteren is een techniek in SQL waarmee je rijen omzet in kolommen in tabeldata. In SQL Server en Oracle gebeurt pivoteren met de PIVOT-operator. De syntax voor de SQL-PIVOT-operator, hieronder weergegeven, heeft drie hoofdonderdelen:

• SELECT: De SELECT-instructie verwijst naar de kolommen die in de SQL-draaitabel worden teruggegeven.

• Subquery: De subquery bevat de gegevensbron of tabel die in de SQL-draaitabel wordt opgenomen.

• PIVOT: De PIVOT-operator bevat de aggregaties en filter die op de draaitabel worden toegepast.

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

Database-specifieke implementaties van SQL PIVOT

SQL Server en Oracle ondersteunen de PIVOT-operator rechtstreeks. MySQL en PostgreSQL hebben echter alternatieve methoden om draaitabellen in SQL te maken.

PIVOT in SQL Server

SQL Server biedt native ondersteuning voor de PIVOT-operator. Hier gebruiken we de PIVOT-operator om rijen om te zetten in kolommen en data samen te vatten met aggregatiefuncties zoals SUM(). We gebruiken ook SQL-clausules zoals WHERE, GROUP BY en ORDER BY voor verfijnde datamanipulatie.

Onderstaand voorbeeld laat zien hoe je de PIVOT-operator gebruikt om data te filteren voor het jaar 2020 of later (WHERE), data te groeperen per stad en jaar (GROUP BY) en de data te sorteren op stad (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;         

Voorbeeldoutput van getransformeerde tabel met SQL PIVOT en veelvoorkomende clausules. Afbeelding door de auteur.

PIVOT in Oracle

Net als in SQL Server gebruikt Oracle ook de PIVOT-operator om rijen in kolommen te transformeren. De syntax van de PIVOT-operator in de Oracle-database verschilt echter licht van die in SQL Server. De onderstaande query laat zien hoe de PIVOT-operator in Oracle wordt gebruikt. Let op dat de kolommen binnen de PIVOT-operator worden gealiast, in tegenstelling tot de buitenste SELECT-instructie in 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;

Pivoteren in MySQL

De MySQL-database ondersteunt de SQL-PIVOT-operator niet. Om SQL-draaitabellen in MySQL te maken, moet je de CASE-instructie met conditionele aggregatie gebruiken. De onderstaande query maakt bijvoorbeeld een draaitabel die de data aggregeert op de som van sales voor verschillende jaren, gegroepeerd en gesorteerd op 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; 

Pivoteren in PostgreSQL

De PostgreSQL-database ondersteunt de SQL-PIVOT-operator evenmin. Daarom is het bij het maken van draaitabellen belangrijk om de CASE-instructie met conditionele aggregatie te gebruiken. De onderstaande query is een voorbeeld van de conditionele CASE-instructies die worden gebruikt om draaitabellen in PostgreSQL te maken.

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

Geavanceerde technieken met SQL PIVOT

Er zijn enkele geavanceerde SQL-pivottechnieken voor het schrijven van complexe queries. In deze sectie bekijken we dynamisch pivoteren, waarmee we queries maken voor draaitabellen waarbij de kolommen die gepivot moeten worden, onbekend zijn. Deze methode gebruikt SQL om de draaitabel tijdens runtime te genereren.

Dynamische PIVOT in SQL Server

De onderstaande query gebruikt PIVOT om de kolom year dynamisch te pivoteren in SQL Server. De query zal onderscheidende jaren ophalen uit de tabel city_sales. Vervolgens zal hij een dynamische PIVOT-query opbouwen en uitvoeren met de opgehaalde jaren.

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

Voorbeeldoutput van tabel met een dynamische SQL PIVOT. Afbeelding door de auteur.

Dynamische PIVOT in Oracle

In de Oracle-database wordt dynamisch pivoteren ondersteund door de dynamische query uit te voeren met de instructie EXECUTE IMMEDIATE. De functie LISTAGG wordt ook gebruikt om de kolomnamen en enkele aanhalingstekens ' ' die in aliassen binnen de pivot worden gebruikt, dynamisch te aggregeren.

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;

Dynamisch pivoteren in MySQL

MySQL ondersteunt geen directe dynamische SQL. Daarom moet je een stored procedure maken voor dynamische PIVOT in MySQL. De onderstaande query laat zien hoe je de stored procedure gebruikt om een dynamische PIVOT-query te maken.

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

Na het aanmaken van de stored procedure moet je de stored procedure aanroepen om de dynamische PIVOT-query uit te voeren:

CALL dynamic_pivot();

Dynamisch pivoteren in PostgreSQL

Op dezelfde manier kun je een dynamische PIVOT in PostgreSQL maken met de volgende query:

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

Conclusie en verder leren

Begrijpen hoe je PIVOT in SQL gebruikt is belangrijk als je data efficiënt wilt transformeren en analyseren. Bij het maken van draaitabellen in SQL is het cruciaal om de verschillende implementaties van de PIVOT-operator in de verschillende databases te leren kennen. Als data-analist moedig ik je aan om je SQL-vaardigheden te blijven oefenen, zodat je leert hoe en wanneer je PIVOT toepast om verschillende datasets te analyseren.

Als je een aankomend data-analist bent die voet aan de grond wil krijgen in de industrie of een meer ervaren analist, raad ik je aan om DataCamp’s cursussen Introduction to SQL en Intermediate SQL te volgen om je data-analysevaardigheden te verbeteren. Ik raad ook onze cursus Data Manipulation in SQL aan, waarin subqueries en andere concepten uit deze tutorial worden behandeld, samen met onze cursus Introduction to SQL Server, die specifiek op SQL Server ingaat.