T-SQL Deep Dive: PIVOT & UNPIVOT

11. März 2014

Dies ist der zweite Teil einer Serie in der ich nützliche T-SQL Befehle vorstellen möchte, welche wenig bekannt sind oder nur selten eingesetzt werden.

Eine Übersicht über alle Artikel ist hier zu finden.

Diesmal widmen wir uns den Funktionen PIVOT und UNPIVOT. Beide sind seit SQL-Server 2005 verfügbar und transformiert Daten innerhalb einer Abfrage in eine normalisiertere oder denormalisierte Form.

PIVOT

Der PIVOT-Befehl ermöglicht es Daten einer Tabelle zu denormalisieren. Dabei werden Zeilenwerte anhand eines Kriteriums aggregiert und auf mehrere Spalte aufgeteilt. Mit anderen Worten die Tabelle wird gedreht (pivotiert) indem Zeilen zu Spalten werden.

Syntax:
SELECT NichtPivotierteSpalte, ErstePivotierteSpalte, ZweitePivotierteSpalte
FROM
    (SELECT Quelle)
    AS query
PIVOT
(
    aggregationsfunktion(Spalte die aggregiert werden soll)
FOR
Spalte mit Werten welche Spalten werden sollen
    IN (ErstePivotierteSpalte, ZweitePivotierteSpalte)
) AS alias

Beispiel:

Wir haben eine Tabelle mit Daten aus verschiedenen Liefersystemen, welche mit Stichtagsdaten gefüllt wird. Wir benötigen eine Übersicht wie viele Daten pro Stichtag pro Liefersystem gekommen sind.

/* Tabelle erstellen */
CREATE TABLE #Staging
(
    Liefersystem char(1),
    Stichtag date,
    Anzahl int
)

/* Tabelle mit Daten befüllen */
INSERT INTO #Staging VALUES 
    ('A', '2014-01-20', 200),
    ('B', '2014-01-20', 100),
    ('C', '2014-01-20', 300),
    ('A', '2014-01-21', 400),
    ('B', '2014-01-21', 500),
    ('C', '2014-01-21', 600),
    ('A', '2014-01-22', 700),
    ('B', '2014-01-22', 800),
    ('C', '2014-01-22', 900),
    ('A', '2014-01-23', 100),
    ('B', '2014-01-23', 200),
    ('C', '2014-01-23', 300),
    ('A', '2014-01-24', 400),
    ('B', '2014-01-24', 500),
    ('C', '2014-01-24', 600)

/* Daten umwandeln */
SELECT Stichtag, A, B, C 
FROM #Staging
PIVOT (SUM(Anzahl) for Liefersystem in ([A], [B], [C])) as pvt
ORDER BY Stichtag DESC

Und so wandeln wir die wenig übersichtlichen Orginaldaten

image

In eine wesentlich übersichtlichere Form:

image

UNPIVOT

Der UNPIVOT-Befehl ist, wie der Name schon vermuten lässt, das Gegenteil des PIVOT Befehls und normalisiert Daten. Es werden also Spalten in Zeilen umgewandelt.

Syntax:
SELECT NichtPivotierteSpalte, SpaltenWert, SpaltenName
FROM Quelle
UNPIVOT
( SpaltenWert FOR SpaltenName in ([Spalte1], [Spalte2], [Spalte3])) up

Beispiel:

Wir haben eine Tabelle mit Personendaten in einer Form wie man sie häufig in alten Access Anwendungen vorfindet. Diese sollen in eine normalisierte Form überführt werden.

/* Tabelle erstellen */
CREATE Table #Person 
(
    Name varchar(100) NOT NULL,
    Mobil varchar(30),
    Arbeit varchar(30),
    Privat varchar(30)
)

/* Beispieldaten einfügen */
INSERT INTO #Person VALUES 
    ('Karl Klammer', '0151 111 111 111', '069 111 111 111', NULL),
    ('Graf Zahl', '0152 222 222 222', NULL, '0661 222 222 222'),
    ('Max Muster', NULL, '069 333 333 333', '0661 333 333 333')

/* Daten umwandeln */
SELECT *
FROM #Person
UNPIVOT
( Nummer FOR Typ in ([Mobil], [Arbeit], [Privat])) up

Hier als Ergebnis die normalisierten Daten:

image

Dynamische Abfragen

Ein Problem ist dem geneigten Leser evtl. bereits aufgefallen, die Spaltennamen bzw. Suchworte sind fester Bestandteil der Abfrage. Doch was wäre wenn in unserem ersten Beispiel ein neues Liefersystem "D" hinzu kommt?

Nun auch dafür gibt es eine Lösung (wenn auch keine elegante):

DECLARE @query nvarchar(max),
        @PivotList varchar(max)
 
SELECT   @PivotList = COALESCE(@PivotList + ', ', N'') + N'[' + Liefersystem + N']'
FROM     (SELECT DISTINCT Liefersystem
          FROM #Staging
         ) AS List

SET @query = 'SELECT Stichtag, ' + @PivotList + ' FROM 
(
    SELECT Stichtag, Anzahl, Liefersystem
    FROM #Staging
) x
PIVOT (SUM(Anzahl) for Liefersystem in (' + @PivotList + ')) as pvt
ORDER BY Stichtag DESC'
 

EXEC sp_executesql @query

Für UNPIVOT wird die benötigte Abfrage noch komplexer, da wir die Metadaten der Tabelle abfragen müssen:

DECLARE @sql AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

SELECT @sql  = STUFF((select ',' + QUOTENAME(name)
           FROM tempdb.sys.columns
           WHERE OBJECT_ID = OBJECT_ID('tempdb..#Person') 
           AND name <> 'Name'
           FOR XML PATH('')), 1, 1, '')

SET @query = 'SELECT *
FROM #Person
UNPIVOT ( Nummer FOR Typ in ('+ @sql + ')) up'

EXEC sp_executesql @query

Fazit

PIVOT und UNPIVOT sind sehr nützliche Funktionen welche das Arbeitsleben erleichtern können. Mit PIVOT können Daten sehr gut für Berichte aufbereitet werden währen UNPIVOT beim Normalisieren von Daten unterstützt. Einziger Wehrmutstropfen ist die fehlende Flexibilität der Abfragen. Zwar kann hierfür dynamisches SQL verwendet werden, aber gerade bei UNPIVOT werden diese sehr schnell komplex und aufwendig…