Wie man SQL PIVOT verwendet

Die schnelle Antwort: Wie man in SQL pivotiert

Der SQL Server PIVOT Operator ist bei der Zusammenfassung von Daten nützlich, da er die Umwandlung von Zeilen in Spalten ermöglicht. Betrachte die folgende Tabelle city_sales, die den allgemeinen Absatz eines Produkts in fünf großen US-Städten zeigt.

Beispiel für eine Tabelle, die mit SQL PIVOT umgewandelt werden soll. Bild vom Autor.

Mit der folgenden Abfrage, die den Operator PIVOT verwendet, können wir mehrere Spalten in der obigen Tabelle drehen.

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

Beispiel für die Umwandlung der Ausgabe mit SQL PIVOT. Bild vom Autor.

Was ist PIVOT in SQL

Pivoting ist eine Technik in SQL, die verwendet wird, um Zeilen in Spalten in tabellarischen Daten umzuwandeln. In SQL Server und Oracle wird das Pivoting mit dem Operator PIVOT durchgeführt. Die Syntax für den SQL-Operator PIVOT, die unten dargestellt ist, besteht aus drei Hauptteilen:

• SELECT: Die Anweisung SELECT verweist auf die Spalten, die in der SQL-Pivot-Tabelle zurückgegeben werden sollen.

• Unterabfrage: Die Unterabfrage enthält die Datenquelle oder Tabelle, die in die SQL-Pivot-Tabelle aufgenommen werden soll.

• PIVOT: Der Operator PIVOT enthält die Aggregationen und Filter, die in der Pivot-Tabelle angewendet werden sollen.

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

Datenbank-spezifische Implementierungen von SQL PIVOT

SQL Server- und Oracle-Datenbanken unterstützen den Operator PIVOT direkt. MySQL und PostgreSQL bieten jedoch alternative Methoden zur Erstellung von Pivot-Tabellen in SQL.

PIVOT in SQL Server

SQL Server bietet native Unterstützung für den Operator PIVOT. Hier werden wir den PIVOT Operator verwenden, um Zeilen in Spalten umzuwandeln und Daten mit Aggregatfunktionen wie SUM() zusammenzufassen. Wir werden auch SQL-Klauseln wie WHERE, GROUP BY und ORDER BY für eine verfeinerte Datenmanipulation verwenden.

Das folgende Beispiel zeigt, wie du den Operator PIVOT verwendest, um Daten für das Jahr 2020 oder später zu filtern (WHERE), Daten nach Stadt und Jahr zu gruppieren (GROUP BY) und die Daten nach Stadt zu sortieren (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;         

Beispiel für die Ausgabe einer umgewandelten Tabelle mithilfe von SQL PIVOT mit gemeinsamen Klauseln. Bild vom Autor.

PIVOT in Oracle

Ähnlich wie SQL Server verwendet auch Oracle den Operator PIVOT, um Zeilen in Spalten umzuwandeln. Die Syntax des PIVOT Operators in der Oracle-Datenbank unterscheidet sich jedoch leicht von der in SQL Server. Die folgende Abfrage zeigt, wie der PIVOT Operator in Oracle erscheint. Beachte, dass die Spalten innerhalb des PIVOT -Operators anders als bei der äußeren SELECT -Anweisung in SQL Server aliased werden.

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

Pivotierung in MySQL

Die MySQL-Datenbank unterstützt den SQL-Operator PIVOT nicht. Um SQL-Pivot-Tabellen in MySQL zu erstellen, musst du die Anweisung CASE mit bedingter Aggregation verwenden. Die folgende Abfrage erstellt zum Beispiel eine Pivot-Tabelle, um die Daten nach der Summe der Verkäufe für verschiedene Jahre zu aggregieren, gruppiert und geordnet nach 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; 

Pivotierung in PostgreSQL

Die PostgreSQL-Datenbank unterstützt auch nicht den SQL-Operator PIVOT. Deshalb ist es wichtig, bei der Erstellung von Pivot-Tabellen die Anweisung CASE mit bedingter Aggregation zu verwenden. Die folgende Abfrage ist ein Beispiel für die bedingten CASE Anweisungen, die zum Erstellen von Pivot-Tabellen in PostgreSQL verwendet werden.

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

Fortgeschrittene Techniken mit SQL PIVOT

Es gibt einige fortgeschrittene SQL-Pivoting-Techniken zum Schreiben komplexer Abfragen. In diesem Abschnitt befassen wir uns mit dem dynamischen Pivoting, das wir unter verwenden, um Abfragen für Pivot-Tabellen zu erstellen, bei denen die zu pivotierenden Spalten unbekannt sind. Diese Methode verwendet SQL, um die Pivot-Tabelle zur Laufzeit zu erstellen.

Dynamischer PIVOT in SQL Server

Die folgende Abfrage verwendet PIVOT, um die Spalte year in SQL Server dynamisch zu drehen. Die Abfrage ruft eindeutige Jahre aus der Tabelle city_sales ab. Unter wird danneine dynamische PIVOT Abfrage mit den abgerufenen Jahren erstellt und ausgeführt.

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

Beispiel für die Ausgabe einer Tabelle mit dem dynamischen SQL PIVOT. Bild vom Autor.

Dynamischer PIVOT in Oracle

In der Oracle-Datenbank wird das dynamische Pivoting unterstützt, indem die dynamische Abfrage mit der Anweisung EXECUTE IMMEDIATE ausgeführt wird. Die Funktion LISTAGG wird auch verwendet, um die Spaltennamen und einfachen Anführungszeichen ' ', die in Aliasen innerhalb des Pivots verwendet werden, dynamisch zu aggregieren.

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;

Dynamisches Pivoting in MySQL

MySQL unterstützt kein direktes dynamisches SQL. Du musst also eine Stored Procedure für die dynamische PIVOT in MySQL erstellen. Die folgende Abfrage zeigt, wie du die Stored Procedure verwendest, um eine dynamische PIVOT Abfrage zu erstellen.

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

Nachdem du die Stored Procedure erstellt hast, musst du die Stored Procedure aufrufen, um die dynamische Abfrage PIVOT auszuführen:

CALL dynamic_pivot();

Dynamisches Pivoting in PostgreSQL

Auf ähnliche Weise kannst du eine dynamische PIVOT in PostgreSQL erstellen, indem du die folgende Abfrage verwendest:

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

Fazit und weiteres Lernen

Wenn du Daten effizient umwandeln und analysieren willst, ist es wichtig zu wissen, wie du PIVOT in SQL verwendest. Beim Erstellen von Pivot-Tabellen in SQL ist es wichtig, die verschiedenen Implementierungen des PIVOT Operators in den verschiedenen Datenbanken zu kennen. Als Datenanalyst ermutige ich dich, deine SQL-Kenntnisse weiter zu trainieren und zu lernen, wie und wann du PIVOT zur Analyse verschiedener Datensätze einsetzen kannst.

Wenn du entweder ein angehender Datenanalyst bist, der in der Branche Fuß fassen will, oder ein erfahrener Analyst, empfehle ich dir die DataCamp-Kurse Introduction to SQL und Intermediate SQL, um deine Datenanalysekenntnisse zu verbessern. Ich empfehle dir auch unseren Kurs Datenbearbeitung in SQL, in dem du Unterabfragen und andere Konzepte lernst, die in diesem Tutorial behandelt werden, sowie unseren Kurs Einführung in SQL Server, in dem es speziell um SQL Server geht.