Kategorien:: Abfragesyntax

UNPIVOT¶

Rotiert eine Tabelle, indem Spalten in Zeilen verwandelt werden. UNPIVOT ist ein relationaler Operator, der zwei Spalten (aus einer Tabelle oder Unterabfrage) zusammen mit einer Spaltenliste nimmt und für jede in der Liste angegebene Spalte eine Zeile generiert. Bei einer Abfrage wird er in der FROM-Klausel nach dem Tabellennamen oder der Unterabfrage angegeben.

UNPIVOT ist nicht das genaue Gegenteil von PIVOT, da sich mit PIVOT vorgenommene Aggregationen nicht rückgängig machen lassen.

Mit diesem Operator kann eine breite Tabelle (z. B. empid, jan_sales, feb_sales, mar_sales) in eine schmalere Tabelle (z. B. empid, month, sales) umgewandelt werden.

Siehe auch:: PIVOT

Syntax¶

SELECT ...
FROM ...
    UNPIVOT [ { INCLUDE | EXCLUDE } NULLS ]
      ( <value_column>
        FOR <name_column> IN ( <column_list> ) )

[ ... ]

Copy

Parameter¶

{ INCLUDE | EXCLUDE } NULLS

Gibt an, ob Zeilen mit NULL-Werten in name_column aufgenommen oder ausgeschlossen werden sollen:

INCLUDE NULLS enthält Zeilen mit NULL-Werten.
EXCLUDE NULLS schließt Zeilen mit NULL-Werten aus.

Standard: EXCLUDE NULLS

value_column

Der Name, der der generierten Spalte zugewiesen werden soll, die mit den Werten aus den Spalten in der Spaltenliste aufgefüllt wird.

name_column

Der Name, der der generierten Spalte zugewiesen werden soll, die mit den Namen der Spalten in der Spaltenliste aufgefüllt wird.

column_list

Die Namen der Spalten in der Quelltabelle oder Unterabfrage, die in eine einzige Pivot-Spalte rotiert werden. name_column wird mit den Spaltennamen aufgefüllt, value_column mit den Spaltenwerten.

column_list kann nur literale Spaltennamen enthalten, keine Unterabfrage.

Beispiele¶

Erstellen Sie eine Tabelle monthly_sales mit der folgenden Struktur und den folgenden Daten:

CREATE OR REPLACE TABLE monthly_sales(
  empid INT,
  dept TEXT,
  jan INT,
  feb INT,
  mar INT,
  apr INT
);

INSERT INTO monthly_sales VALUES
  (1, 'electronics', 100, 200, 300, 100),
  (2, 'clothes', 100, 300, 150, 200),
  (3, 'cars', 200, 400, 100, 50),
  (4, 'appliances', 100, NULL, 100, 50);

SELECT * FROM monthly_sales;

Copy

+-------+-------------+-----+------+------+-----+
| EMPID | DEPT        | JAN | FEB  | MAR  | APR |
|-------+-------------+-----+------+------+-----|
|     1 | electronics | 100 | 200  | 300  | 100 |
|     2 | clothes     | 100 | 300  | 150  | 200 |
|     3 | cars        | 200 | 400  | 100  |  50 |
|     4 | appliances  | 100 | NULL | 100  |  50 |
+-------+-------------+-----+------+------+-----+

Entpivotieren Sie die einzelnen Monatsspalten, um anhand von month einen einzelnen sales-Wert für die jeweiligen Mitarbeiter zurückzugeben:

SELECT *
  FROM monthly_sales
    UNPIVOT (sales FOR month IN (jan, feb, mar, apr))
  ORDER BY empid;

Copy

+-------+-------------+-------+-------+
| EMPID | DEPT        | MONTH | SALES |
|-------+-------------+-------+-------|
|     1 | electronics | JAN   |   100 |
|     1 | electronics | FEB   |   200 |
|     1 | electronics | MAR   |   300 |
|     1 | electronics | APR   |   100 |
|     2 | clothes     | JAN   |   100 |
|     2 | clothes     | FEB   |   300 |
|     2 | clothes     | MAR   |   150 |
|     2 | clothes     | APR   |   200 |
|     3 | cars        | JAN   |   200 |
|     3 | cars        | FEB   |   400 |
|     3 | cars        | MAR   |   100 |
|     3 | cars        | APR   |    50 |
|     4 | appliances  | JAN   |   100 |
|     4 | appliances  | MAR   |   100 |
|     4 | appliances  | APR   |    50 |
+-------+-------------+-------+-------+

Die vorherige SELECT-Anweisung schließt NULL-Werte standardmäßig aus. Daher sind keine Zeile für Haushaltsgeräte im Februar in den Ergebnissen enthalten. Um NULL-Werte in die Ergebnisse einzubeziehen, führen Sie die folgende SQL-Anweisung aus:

SELECT *
  FROM monthly_sales
    UNPIVOT INCLUDE NULLS (sales FOR month IN (jan, feb, mar, apr))
  ORDER BY empid;

Copy

+-------+-------------+-------+-------+
| EMPID | DEPT        | MONTH | SALES |
|-------+-------------+-------+-------|
|     1 | electronics | JAN   |   100 |
|     1 | electronics | FEB   |   200 |
|     1 | electronics | MAR   |   300 |
|     1 | electronics | APR   |   100 |
|     2 | clothes     | JAN   |   100 |
|     2 | clothes     | FEB   |   300 |
|     2 | clothes     | MAR   |   150 |
|     2 | clothes     | APR   |   200 |
|     3 | cars        | JAN   |   200 |
|     3 | cars        | FEB   |   400 |
|     3 | cars        | MAR   |   100 |
|     3 | cars        | APR   |    50 |
|     4 | appliances  | JAN   |   100 |
|     4 | appliances  | FEB   |  NULL |
|     4 | appliances  | MAR   |   100 |
|     4 | appliances  | APR   |    50 |
+-------+-------------+-------+-------+

Diese Ausgabe enthält eine Zeile für Haushaltsgeräte im Februar.

Anstatt alle Spalten mit * auszuwählen, können Sie bestimmte Spalten in die Liste SELECT aufnehmen und auf UNPIVOT value_column und name_column verweisen. Das folgende Beispiel ähnelt dem vorherigen Beispiel, aber es gibt die value_column sales und die name_column month in der SELECT-Liste an. Die Abfrage schließt die Spalte empid aus:

SELECT dept, month, sales
  FROM monthly_sales
    UNPIVOT INCLUDE NULLS (sales FOR month IN (jan, feb, mar, apr))
  ORDER BY dept;

Copy

+-------------+-------+-------+
| DEPT        | MONTH | SALES |
|-------------+-------+-------|
| appliances  | JAN   |   100 |
| appliances  | FEB   |  NULL |
| appliances  | MAR   |   100 |
| appliances  | APR   |    50 |
| cars        | JAN   |   200 |
| cars        | FEB   |   400 |
| cars        | MAR   |   100 |
| cars        | APR   |    50 |
| clothes     | JAN   |   100 |
| clothes     | FEB   |   300 |
| clothes     | MAR   |   150 |
| clothes     | APR   |   200 |
| electronics | JAN   |   100 |
| electronics | FEB   |   200 |
| electronics | MAR   |   300 |
| electronics | APR   |   100 |
+-------------+-------+-------+