SQL Draait Rijen naar Kolommen: Een Uitgebreide Gids

Wat betekent het om rijen naar kolommen te draaien in SQL?

Pivoteren in SQL verwijst naar het transformeren van gegevens van een rijgebaseerd formaat naar een kolomgebaseerd formaat. Deze transformatie is handig voor rapportage en data-analyse, waardoor een meer gestructureerd en compact gegevensoverzicht mogelijk is. Het omzetten van rijen naar kolommen stelt gebruikers ook in staat om gegevens te analyseren en samen te vatten op een manier die belangrijke inzichten duidelijker naar voren brengt.

Bekijk het volgende voorbeeld: Ik heb een tabel met dagelijkse verkooptransacties, en elke rij registreert de datum, productnaam en verkoopbedrag.

Door deze tabel te pivoteren, kan ik deze herstructureren om elk product als een kolom weer te geven, met verkoopgegevens voor elke datum onder de overeenkomstige kolom. Merk ook op dat er een aggregatie plaatsvindt.

Traditioneel vereisten draaitabelbewerkingen complexe SQL-query’s met voorwaardelijke aggregatie. In de loop der tijd zijn SQL-implementaties geëvolueerd, waarbij veel moderne databases nu de PIVOT– en UNPIVOT-operatoren bevatten om efficiëntere en eenvoudigere transformaties mogelijk te maken.

Begrip van SQL Pivot Rijen naar Kolommen

De SQL pivot-operatie transformeert gegevens door rijwaarden om te zetten in kolommen. Dit is de basis syntaxis en structuur van SQL pivot met de volgende onderdelen:

• SELECT: De SELECT-verklaring verwijst naar de kolommen die geretourneerd moeten worden in de SQL-pivot tabel.

• Subquery: De subquery bevat de gegevensbron of tabel die moet worden opgenomen in de SQL-pivot tabel.

• PIVOT: De PIVOT operator bevat de aggregaties en filters die moeten worden toegepast in de pivot-tabel.

-- Selecteer statische kolommen en gepivoteerde kolommen
SELECT <static columns>, [pivoted columns]
FROM
    (
        -- Subquery die de brongegevens voor de pivot definieert
        <subquery that defines data>
    ) AS source
PIVOT
(
    -- Aggregatiefunctie toegepast op de waardekolom, waardoor nieuwe kolommen worden gemaakt
    <aggregation function>(<value column>)
    FOR <column to pivot> IN ([list of pivoted columns])
) AS pivot_table;

Laten we naar het volgende stapsgewijze voorbeeld kijken om te laten zien hoe rijen worden gepivoteerd naar kolommen in SQL. Neem de onderstaande SalesData tabel in overweging.

Voorbeeld van een tabel om te transformeren met behulp van de SQL PIVOT-operator. Afbeelding door auteur.

Ik wil deze gegevens pivoten om de dagelijkse verkoop van elk product te vergelijken. Ik begin door de subquery te selecteren die de PIVOT-operator zal structureren.

-- Subquery die de brongegevens voor de pivot definieert
SELECT Date, Product, Sales
FROM SalesData;

Nu zal ik de PIVOT-operator gebruiken om Product-waarden om te zetten in kolommen en Sales te aggregeren met behulp van de SUM-operator.

-- Selecteer Datum en gepivoteerde kolommen voor elk product
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- Subquery om Datum, Product en Sales kolommen op te halen
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- Aggregeer Sales per Product, waarbij productwaarden naar kolommen worden gepivoteerd
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

Voorbeeld van uitvoertransformatie met behulp van SQL-pivotrijen naar kolommen. Afbeelding door auteur.

Hoewel het draaien van gegevens gegevenssamenvatting vereenvoudigt, heeft deze techniek potentiële problemen. Dit zijn de mogelijke uitdagingen met SQL-pivot en hoe deze aan te pakken.

• Dynamische kolomnamen: Als de waarden om te draaien (bijv. producttypen) onbekend zijn, zal het hardcoderen van kolomnamen niet werken. Sommige databases, zoals SQL Server, ondersteunen dynamische SQL met opgeslagen procedures om dit probleem te vermijden, terwijl andere vereisen dat dit wordt afgehandeld op applicatieniveau.

• Omgaan met NULL-waarden: Wanneer er geen gegevens zijn voor een specifieke gepivoteerde kolom, kan het resultaat NULL bevatten. U kunt COALESCE gebruiken om NULL-waarden te vervangen door nul of een andere aanduiding.

• Compatibiliteit tussen databases: Niet alle databases ondersteunen de PIVOT-operator direct. U kunt vergelijkbare resultaten behalen met CASE-statements en conditionele aggregatie als uw SQL-dialect dit niet ondersteunt.

SQL Pivot Rijen naar Kolommen: Voorbeelden en Gebruiksscenario’s

Verschillende methoden worden gebruikt om gegevens te pivoteren in SQL, afhankelijk van de gebruikte database of andere vereisten. Terwijl de PIVOT-operator vaak wordt gebruikt in SQL Server, maken andere technieken, zoals de CASE-statements, vergelijkbare database-transformaties mogelijk zonder directe ondersteuning voor PIVOT. Ik zal de twee gebruikelijke methoden voor het pivoteren van gegevens in SQL behandelen en praten over de voor- en nadelen.

Gebruik van de PIVOT-operator

De PIVOT-operator, beschikbaar in SQL Server, biedt een eenvoudige manier om rijen naar kolommen te pivoteren door een aggregatiefunctie te specificeren en de te pivoteren kolommen te definiëren.

Bekijk de volgende tabel met de naam sales_data.

Voorbeeld Orders-tabel om te transformeren met behulp van de PIVOT-operator. Afbeelding door auteur.

Ik zal de PIVOT-operator gebruiken om de gegevens samen te voegen, zodat het totale sales_revenue per jaar in kolommen wordt weergegeven.

-- Gebruik PIVOT om de omzet per jaar samen te voegen
SELECT *
FROM (
    -- Selecteer de relevante kolommen uit de brontabel
    SELECT sale_year, sales_revenue
    FROM sales_data
) AS src
PIVOT (
    -- Voeg de omzet per jaar samen
    SUM(sales_revenue)
    -- Maak kolommen voor elk jaar
    FOR sale_year IN ([2020], [2021], [2022], [2023])
) AS piv;

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

Het gebruik van de PIVOT-operator heeft de volgende voordelen en beperkingen:

• Voordelen: De methode is efficiënt wanneer kolommen goed geïndexeerd zijn. Het heeft ook een eenvoudige, meer leesbare syntaxis.

• Beperkingen: Niet alle databases ondersteunen de PIVOT-operator. Het vereist het van tevoren specificeren van de kolommen, en dynamisch pivoteren vereist extra complexiteit.

Handmatig pivoteren met CASE-statements

Je kunt ook de CASE-statements gebruiken om gegevens handmatig te pivoteren in databases die de PIVOT-operator niet ondersteunen, zoals MySQL en PostgreSQL. Deze benadering maakt gebruik van conditionele aggregatie door elke rij te evalueren en waarden conditioneel toe te wijzen aan nieuwe kolommen op basis van specifieke criteria.

Bijvoorbeeld, we kunnen gegevens handmatig pivoteren in dezelfde sales_data-tabel met CASE-statements.

-- Aggregeer de omzet per jaar met CASE-statements
SELECT 
    -- Bereken de totale omzet voor elk jaar
    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;

Voorbeeld van outputtransformatie met behulp van SQL CASE-instructie. Afbeelding door auteur.

Het gebruik van de CASE-instructie voor transformatie heeft de volgende voordelen en beperkingen:

• Voordelen: De methode werkt in alle SQL-databases en is flexibel voor het dynamisch genereren van nieuwe kolommen, zelfs wanneer productnamen onbekend zijn of vaak veranderen.

• Beperkingen: Query’s kunnen complex en lang worden als er veel kolommen zijn om te pivoteren. Vanwege de meerdere conditionele controles presteert de methode iets langzamer dan de PIVOT-operator.

Prestatie-overwegingen bij het omzetten van rijen naar kolommen

Het omzetten van rijen naar kolommen in SQL kan prestatie-implicaties hebben, vooral bij het werken met grote datasets. Hier zijn enkele tips en best practices om u te helpen efficiënte pivot-query’s te schrijven, hun prestaties te optimaliseren en veelvoorkomende valkuilen te vermijden.

Beste praktijken

De volgende zijn de beste werkwijzen om uw query’s te optimaliseren en de prestaties te verbeteren.

• Indexeringsstrategieën: Goede indexering is cruciaal voor het optimaliseren van pivot-query’s, waardoor SQL gegevens sneller kan ophalen en verwerken. Indexeer altijd de kolommen die vaak worden gebruikt in de WHERE-clausule of de kolommen waarop u groepeert om de scantijden te verminderen.

• Vermijd Geneste Pivots: Het stapelen van meerdere pivot-operaties in één query kan moeilijk te lezen zijn en langzamer uitgevoerd worden. Vereenvoudig door de query op te splitsen in delen of door een tijdelijke tabel te gebruiken.

• Beperk Kolommen en Rijen in Pivot: Alleen pivot-kolommen zijn nodig voor de analyse, aangezien het pivoteren van veel kolommen veel resources kan kosten en grote tabellen kan creëren.

Vermijden van veelvoorkomende valkuilen

De volgende zijn de veelvoorkomende fouten die u kunt tegenkomen in pivot query’s en hoe u ze kunt vermijden.

• Onnodige Volledige Tabelscans: Pivot query’s kunnen volledige tabelscans activeren, vooral als er geen relevante indexen beschikbaar zijn. Voorkom volledige tabelscans door sleutelkolommen te indexeren en gegevens te filteren voordat u de pivot toepast.

• Het Gebruik van Dynamische SQL voor Frequente Pivoting:Het gebruik van dynamische SQL kan de prestaties vertragen vanwege de query-hercompilatie. Om dit probleem te voorkomen, cacheer of beperk dynamische pivots tot specifieke scenario’s en overweeg dynamische kolommen in de toepassingslaag te verwerken wanneer mogelijk.

• Agregatie op grote datasets zonder vooraf te filteren:Agregatiefuncties zoals SUM of COUNT op grote datasets kunnen de prestaties van de database vertragen. In plaats van de hele dataset te pivoteren, filtert u eerst de gegevens met behulp van een WHERE-clausule.

• NULL-waarden in gepivoteerde kolommen: Pivoteerbewerkingen produceren vaak NULL-waarden wanneer er geen gegevens zijn voor een specifieke kolom. Deze kunnen query’s vertragen en resultaten moeilijker te interpreteren maken. Om dit probleem te voorkomen, gebruikt u functies zoals COALESCE om NULL-waarden te vervangen door een standaardwaarde.

• Alleen testen met voorbeeldgegevens: Pivot-query’s kunnen zich anders gedragen bij grote datasets vanwege verhoogd geheugen- en verwerkingsgebruik. Test altijd pivot-query’s met echte of representatieve gegevensvoorbeelden om de prestatie-impact nauwkeurig te beoordelen.

Probeer onze carrièreroute voor SQL Server Developer, die alles behandelt, van transacties en foutafhandeling tot het verbeteren van queryprestaties.

Implementaties Specifiek voor Databases

Pivot-operaties verschillen aanzienlijk tussen databases zoals SQL Server, MySQL en Oracle. Elk van deze databases heeft specifieke syntaxis en beperkingen. Ik zal voorbeelden behandelen van het pivoteren van gegevens in de verschillende databases en hun belangrijkste kenmerken.

SQL Server

SQL Server biedt een ingebouwde PIVOT-operator, die eenvoudig is bij het pivoteren van rijen naar kolommen. De PIVOT-operator is gemakkelijk te gebruiken en integreert met de krachtige aggregatiefuncties van SQL Server. De belangrijkste kenmerken van pivoteren in SQL zijn onder andere:

• Directe ondersteuning voor PIVOT en UNPIVOT: De PIVOT-operator van SQL Server maakt snelle transformatie van rij naar kolom mogelijk. De UNPIVOT-operator kan dit proces ook omkeren.

• Aggregatie-opties: De PIVOT-operator maakt verschillende aggregatiefuncties mogelijk, zoals SUM, COUNT en AVG.

De beperking van de PIVOT-operator in SQL Server is dat het vereist dat kolomwaarden die moeten worden gepivoteerd van tevoren bekend zijn, waardoor het minder flexibel is voor dynamisch veranderende gegevens.

In het onderstaande voorbeeld zet de PIVOT-operator Product-waarden om in kolommen en aggregeert Sales met behulp van de SUM-operator.

-- Selecteer Datum en gepivoteerde kolommen voor elk product
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- Subquery om Datum, Product en Sales kolommen op te halen
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- Aggregeer Sales per Product, waarbij productwaarden worden gepivoteerd naar kolommen
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

Ik raad aan om de cursus Introductie tot SQL Server van DataCamp te volgen om de basis van SQL Server voor gegevensanalyse onder de knie te krijgen.

MySQL

MySQL biedt geen native ondersteuning voor de PIVOT-operator. U kunt echter de CASE-verklaring gebruiken om rijen handmatig naar kolommen te pivoten en andere aggregeerfuncties zoals SUM, AVG en COUNT te combineren. Hoewel deze methode flexibel is, kan het complex worden als u veel kolommen moet pivoten.

De onderstaande query bereikt dezelfde output als het SQL Server PIVOT-voorbeeld door de verkoop voor elk product conditioneel samen te voegen met behulp van de CASE-verklaring.

-- Selecteer Datum en gepivoteerde kolommen voor elk product
SELECT 
    Date,
    -- Gebruik CASE om een kolom voor Laptop- en Muisverkopen te maken
    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 ondersteunt de PIVOT-operator, die de eenvoudige transformatie van rijen naar kolommen mogelijk maakt. Net als SQL Server moet je expliciet de kolommen voor transformatie specificeren.

In de onderstaande query zet de PIVOT-operator ProductName-waarden om in kolommen en aggregeert SalesAmount met behulp van de SUM-operator.

SELECT *
FROM (
    -- Selectie van brondata
    SELECT SaleDate, ProductName, SaleAmount FROM SalesData
)
PIVOT (
    -- Verkoopaggregatie per product, waarbij gepivoteerde kolommen worden gemaakt
    SUM(SaleAmount)
    FOR ProductName IN ('Laptop' AS Laptop, 'Mouse' AS Mouse)
);

Voorbeeld van outputtransformatie met behulp van de SQL PIVOT-operator in Oracle. Afbeelding door auteur.

Geavanceerde technieken voor het omzetten van rijen naar kolommen in SQL

Geavanceerde technieken voor het omzetten van rijen naar kolommen zijn handig wanneer je flexibiliteit nodig hebt bij het verwerken van complexe gegevens. Dynamische technieken en het tegelijkertijd verwerken van meerdere kolommen stellen je in staat om gegevens te transformeren in scenario’s waar statisch pivoteren beperkt is. Laten we deze twee methoden in detail verkennen.

Dynamische pivots

Dynamische pivots stellen je in staat om pivotquery’s te maken die automatisch aanpassen aan veranderingen in de gegevens. Deze techniek is bijzonder handig wanneer je kolommen hebt die vaak veranderen, zoals productnamen of categorieën, en je wilt dat je query automatisch nieuwe vermeldingen bevat zonder dat je deze handmatig hoeft bij te werken.

Stel dat we een SalesData tabel hebben en een dynamische draaitabel kunnen maken die zich aanpast als er nieuwe producten worden toegevoegd. In de onderstaande query bouwt @columns dynamisch de lijst met gedraaide kolommen op, en sp_executesql voert de gegenereerde SQL uit.

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
-- Stap 1: Genereer een lijst van unieke producten om te draaien
SELECT @columns = STRING_AGG(QUOTENAME(Product), ', ') 
FROM (SELECT DISTINCT Product FROM SalesData) AS products;
-- Stap 2: Bouw de dynamische 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;';
-- Stap 3: Voer de dynamische SQL uit
EXEC sp_executesql @sql;

Omgaan met meerdere kolommen

In scenario’s waarin u meerdere kolommen tegelijk moet draaien, zult u de PIVOT-operator en aanvullende aggregatietechnieken gebruiken om meerdere kolommen in dezelfde query te maken.

In het onderstaande voorbeeld heb ik de Sales– en Quantity-kolommen gedraaid op basis van Product.

-- Pivot Verkoop en Hoeveelheid voor Laptop en Muis op Datum
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 voor Verkoop
        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 voor Hoeveelheid
        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;

Voorbeeld outputtransformatie van meerdere kolommen met behulp van de SQL PIVOT-operator. Afbeelding door auteur.

Het pivoteren van meerdere kolommen maakt meer gedetailleerde rapporten mogelijk door meerdere attributen per item te pivoteren, wat rijkere inzichten mogelijk maakt. De syntaxis kan echter complex zijn, vooral als er veel kolommen zijn. Hardcodering kan nodig zijn, tenzij gecombineerd met dynamische pivottechnieken, wat verdere complexiteit toevoegt.

Conclusie

Het pivoteren van rijen naar kolommen is een SQL-techniek die de moeite waard is om te leren. Ik heb SQL-pivottechnieken gezien die worden gebruikt om een cohortretentietabel te maken, waarbij je de gebruikersretentie in de loop van de tijd kunt volgen. Ik heb ook SQL-pivottechnieken gezien die worden gebruikt bij het analyseren van enquêtegegevens, waarbij elke rij een respondent vertegenwoordigt en elke vraag kan worden gepivot naar zijn eigen kolom.

Onze cursus Rapportage in SQL is een geweldige optie als je meer wilt leren over het samenvatten en voorbereiden van gegevens voor presentatie en/of dashboardbouw. Onze carrièrepaden Associate Data Analist in SQL en Associate Data Engineer in SQL zijn ook een geweldig idee en voegen veel toe aan elk cv, dus schrijf je vandaag nog in.