Transponieren Sie Spalten in Zeilen in PostgreSQL

Um Werte, die in mehreren Spalten von PostgreSQL gespeichert sind, in Zeilen umzuwandeln, legen Sie die Werte zunächst in Arrays ab und erweitern diese mit unnest(). Die Funktion gibt die Array-Elemente als Zeilen aus. crosstab() führt die entgegengesetzte Umformung aus: Sie pivotiert kategorisierte Zeilen in Spalten und eignet sich daher nicht für die Umwandlung von Spalten in Zeilen.

Beispielsweise lassen sich die vier Kurswerte val1 bis val4 so anordnen, dass jeder Wert in einer eigenen Zeile steht und dem Schüler sowie dem Abschluss an derselben Position zugeordnet ist. Das ist hilfreich, wenn ein breiter Datensatz verarbeitet oder als Folge von Attribut-Wert-Paaren angezeigt werden soll. Die konkrete Abfrage hängt davon ab, ob die Werte bereits in Arrays oder in separaten Tabellenspalten vorliegen.

Spalten in Zeilen in PostgreSQL umwandeln

Eine Tabelle mit einer Spalte pro Attribut ist eine breite Darstellung. Um ausgewählte Werte umzuformen, erstellen Sie Arrays in einer einheitlichen Reihenfolge und erweitern sie gemeinsam. Das erste Array kann Bezeichnungen enthalten, die übrigen die zugehörigen Werte. Jede Ergebniszeile enthält dann die Bezeichnung und die Werte an der jeweiligen Position.

Die Funktion unnest() von PostgreSQL erweitert Arrays. Sie entdeckt jedoch nicht automatisch alle Tabellenspalten und transponiert sie auch nicht allgemein. In der Abfrage wählen Sie die Spalten und ihre Reihenfolge selbst aus. Dadurch bleibt die Umformung nachvollziehbar, und Werte aus unterschiedlichen Attributen werden nicht versehentlich einander zugeordnet.

Die Beispieltabelle erstellen

Das folgende SQL definiert die Beispieltabelle und fügt die Datensätze ein, die in den weiteren Beispielen verwendet werden. Std_no bleibt nullable, damit das Schema zu den vorgegebenen Daten passt: Die INSERT-Anweisungen lassen die Spalte aus, daher haben diese Zeilen keine Schülernummer. StdName, StdDegree und CourseName enthalten die vier Abschluss- und Kurswertpaare für zwei Schüler.

Verwenden Sie dieses Schema: CREATE TABLE Student (Std_no int, StdName text, StdDegree text, CourseName text);

Fügen Sie die Beispielzeilen ein:

INSERT INTO Student (StdName, StdDegree, CourseName) VALUES ('test1','att1','val1'), ('test1','att2','val2'), ('test1','att3','val3'), ('test1','att4','val4'), ('test2','att1','val5'), ('test2','att2','val6'), ('test2','att3','val7'), ('test2','att4','val8');

Die Zeilen sind nach StdName gruppiert. Für test1 reicht die Zuordnung vom Abschluss zum Kurswert von att1 bis val1 bis hin zu att4 und val4. Behalten Sie diese Reihenfolge beim Erstellen paralleler Arrays bei, damit jede Bezeichnung dem vorgesehenen Wert zugeordnet bleibt.

Mit crosstab() Zeilen in Spalten pivotieren

crosstab() ist hilfreich, wenn die Eingabe aus Zeilen mit einer Zeilenkennung, einer Kategorie und einem Wert besteht und das gewünschte Ergebnis eine Ausgabespalte pro Kategorie enthält. Das ist eine Pivotierung von Zeilen in Spalten und damit die umgekehrte Richtung zur Umwandlung von Spalten in Zeilen. Der Abschnitt grenzt diese ähnlich benannte, aber andere Umformung ab.

Die Funktion wird von der tablefunc-Erweiterung von PostgreSQL bereitgestellt. Eine typische Einrichtung lautet CREATE EXTENSION IF NOT EXISTS tablefunc;. Die Quellabfrage muss einen Zeilennamen, eine Kategorie und einen Wert zurückgeben; außerdem muss der Ergebnistyp deklariert werden. Die Form crosstab(source_sql, category_sql) verwendet beispielsweise eine Abfrage für die Daten und eine weitere, um die Reihenfolge der Kategorien festzulegen. Die Ausgabespalten müssen der deklarierten Ergebnisstruktur entsprechen; crosstab() leitet für ein festes Abfrageergebnis keine variable Anzahl von Spalten ab.

Verwenden Sie crosstab(), wenn Kategorien in einem Bericht zu Spalten werden sollen. Für die in diesem Artikel behandelte Umwandlung von Spalten in Zeilen ist die Funktion ungeeignet: Zweck und Ausgaberichtung unterscheiden sich.

Mit unnest() Arrays in Zeilen umwandeln

unnest(anyarray) erweitert ein Array zu einer Menge von Zeilen. Um mehrere parallele Arrays in aufeinander abgestimmte Spalten umzuwandeln, verwenden Sie die Mehr-Array-Form in der FROM-Klausel und vergeben Aliase für die resultierenden Spalten. Das folgende Python-Beispiel veranschaulicht die positionsbasierte Erweiterung. Es ist ein eigenständiges Beispiel und keine Abfrage für PostgreSQL:

Der Code und die angezeigten Bezeichnungen oder Ausgaben in diesem Abschnitt bleiben auf Englisch.

labels = ["StdName", "StdDegree", "CourseName"]
values = ["test1", "att1", "val1"]
for label, value in zip(labels, values):
    print(f"{label}: {value}")

StdName: test1
StdDegree: att1
CourseName: val1

Um Spalten aus der Beispieltabelle zu erweitern, wählen Sie eine Quellzeile aus und übergeben deren Felder zusammen mit einem Array ihrer Bezeichnungen. Diese Abfrage für PostgreSQL wählt die Zeile für test1 mit dem Abschluss att1 aus und gibt anschließend für jede ausgewählte Spalte ein Attribut-Wert-Paar zurück:

SELECT t.attribute, t.value FROM Student AS s CROSS JOIN LATERAL unnest(ARRAY['StdName','StdDegree','CourseName']::text[], ARRAY[s.StdName, s.StdDegree, s.CourseName]::text[]) AS t(attribute, value) WHERE s.StdName = 'test1' AND s.StdDegree = 'att1';

Das Ergebnis hat zwei Spalten, attribute und value, sowie drei Zeilen: StdName mit test1, StdDegree mit att1 und CourseName mit val1. Jeder Wert stammt aus derselben Tabellenzeile; eine Schülernummer kommt daher nicht hinzu. Die Abfrage für PostgreSQL wird als SQL gezeigt und wurde nicht in der bereitgestellten Python-Laufzeit ausgeführt.

Beide Arrays enthalten kompatible Textelemente: eines enthält die Bezeichnungen, das andere die Werte der ausgewählten Spalten. Die Mehr-Array-Form von unnest() in PostgreSQL füllt kürzere Arrays mit NULL-Werten bis zur Länge des längsten Arrays auf. Wenn jede Position einen vollständigen Datensatz darstellen soll, müssen die Arrays gleich lang sein. Andernfalls müssen Sie diese Nullwerte gezielt behandeln.

Eine andere Form verwendet Aufrufe von unnest() in der SELECT-Liste. Bei einem einzelnen Array liefert SELECT unnest(ARRAY['att1','att2','att3','att4']) AS degree; eine Ergebniszeile pro Element. Mehrere mengenrückgebende Funktionen in einer SELECT-Liste sind schwieriger nachzuvollziehen, und ihr Verhalten hängt von der Version ab. Wenn aufeinander abgestimmte Spalten gewünscht sind, verwenden Sie die Mehr-Array-Form in FROM. Sie macht die Beziehung zwischen den Arrays ausdrücklich sichtbar und benennt die Ausgabespalten klar.

Das Beispiel ist bewusst klein gehalten. Um Werte aus einer echten Tabelle umzuformen, wählen Sie die benötigten Spalten in einer festgelegten Reihenfolge in Arrays aus oder verwenden Sie eine laterale Abfrage, wenn jede Quellzeile eigene Array-Werte hat. unnest() erweitert Arrays; die Funktion wandelt nicht automatisch beliebige Tabellenspalten in eine normalisierte Menge von Attribut-Wert-Zeilen um.

Die richtige Methode auswählen

Verwenden Sie unnest(), wenn Array-Elemente zu Zeilen werden sollen, auch bei parallelen Arrays, die als aufeinander abgestimmte Ausgabespalten erscheinen sollen. Achten Sie auf eine einheitliche Länge und Reihenfolge der Elemente und benennen Sie die Ausgabespalten mit einer Aliasliste. Besteht die Quelle aus einer regulären Tabelle mit voneinander unabhängigen skalaren Spalten, legen Sie fest, welche Felder in Arrays aufgenommen werden sollen, oder erstellen Sie eine Abfrage, die zur Tabellenstruktur passt.

Verwenden Sie crosstab(), wenn kategorisierte Eingabezeilen zu Ausgabespalten werden sollen. Die Richtung ist umgekehrt: crosstab() pivotiert Zeilen in Spalten, während unnest() Array-Elemente in Zeilen erweitert. Wenn Sie die Funktion anhand der gewünschten Ergebnisform auswählen, wenden Sie keine Pivot-Funktion auf die falsche Aufgabe an.