Rijen naar kolommen via PIVOT

Met UNPIVOT kan je kolom-gebaseerde gegevens omzetten naar rijen. Dit is bijvoorbeeld handig als je een geïmporteerd CSV-bestand of Excel-werkblad wilt verwerken op dynamische wijze via SQL. De omgekeerde functie is PIVOT: met deze draaitabelfunctie maak je van rijen kolommen. Dit is handig voor bepaalde presentaties van gegevens of het exporteren van gegevens uit de database.

De werking van PIVOT is vergelijkbaar met UNPIVOT: je geeft aan welke kolommen je wilt tonen, waar je de brondata vandaan haalt (dit kan een simpele tabel zijn of een samenstelling van tabellen) en in de PIVOT-functie geef je aan welke kolommen je wilt aanmaken en welke dat ze dienen te bevatten.

Voor het voorbeeld maak ik een nieuwe tabel aan in de database:

CREATE TABLE dbo.ProductData (ID int, KeyKolom nvarchar(255), ValueKolom nvarchar(255));

INSERT INTO dbo.ProductData (ID, KeyKolom, ValueKolom) VALUES
(1, 'Kleur', 'Blauw'),
(1, 'Maat', 'XL'),
(2, 'Kleur', 'Rood'),
(2, 'Maat', 'XL'),
(2, 'Hoogte', '100')
;

SELECT * FROM dbo.ProductData;

Het resultaat hiervan is:

Als resultaat willen we de ID hebben en per ID Kleur en Maat, waarbij Kleur en Maat kolommen zijn. Kleur en Maat zijn geen kolommen in de database (alleen waardes in een tabel), maar hiervoor is dus PIVOT:

SELECT
       ID,
       Kleur,
       Maat
FROM

       (SELECT ID, ValueKolom, KeyKolom FROM dbo.ProductData) BRON_TABEL

PIVOT(
       MAX(ValueKolom)
             FOR
       KeyKolom IN(
             Kleur,
             Maat
       )
) AS PIVOT_TABEL

Het resultaat is:

In het voorbeeld wordt gebruik gemaakt van MAX(). Dat is omdat de PIVOT-functie een aggregate-functie verwacht: Als er meerdere rijen zijn met een bepaalde waarde, moet worden aangegeven welke waarde wordt gebruikt. In ht geval van tekstwaardes is het niet erg relevant en dus gebruik ik hier MAX, maar je kunt ook SUM of AVG gebruiken. Deze zijn nuttig in bijvoorbeeld financiele overzichten:

CREATE TABLE dbo.OmzetBestellingen (Datum datetime, Omzet money);

INSERT INTO dbo.OmzetBestellingen (Datum, Omzet) VALUES
('20171024', 100),
('20171024', 200),
('20171025', 234),
('20171125', 50);
('20161024', 25)

SELECT * FROM dbo.OmzetBestellingen;

Resultaat:

Om de totaalomzet per maand per jaar, voor de maanden september, oktober en november op te halen, geef je het maandnummer en jaartal terug in de brontabel en maak je gebruik van SUM() om de omzet bij elkaar op te tellen:

SELECT DISTINCT
       Jaar,
       [9] AS [September],
       [10] AS [Oktober],
       ISNULL([11], 0) AS [November]
FROM

       (SELECT MONTH(Datum) as Maand, YEAR(Datum) as Jaar, Omzet FROM dbo.OmzetBestellingen) BRON_TABEL

PIVOT(
       SUM(Omzet)
             FOR
       Maand IN (
             [9],
             [10],
             [11]
       )
) P

Resultaat:

In bovenstaande voorbeeld staat een ISNULL-functie bij November, om aan te geven dat bij een ontbrekende waarde, het getal 0 moet worden gebruikt. Bij PIVOT geldt dat als een kolomwaarde bij een bepaalde rij niet wordt gevonden, NULL wordt teruggegeven. In het voorbeeld aan het begin van deze blog zou dit ook gelden voor de waarde [Hoogte] bij rij met ID 1, als Hoogte meegenomen zou worden in de PIVOT-functie.