Rijen naar kolommen via PIVOT

Geschreven door: op donderdag 26 oktober 2017

Leestijd: 3 minuten

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:

sql-results-1.jpg

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:

sql-results-2.jpg

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:

sql-results-3.jpg

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:

sql-results-4.jpg

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.

 


Andere blogartikelen

  • Herschrijf regels binnen IIS: Introductie

    Geschreven door: op maandag 28 juni 2021

    Herschrijf regels binnen IIS: IntroductieIIS herschrijf regels zijn een krachtige tool om URls te herschrijven zodat deze bijvoorbeeld beter bruikbaar en begrijpelijker zijn voor de gebruikers. Door ...

    Bekijk het artikel »
  • Voorwaardelijke URL Rewrites

    Geschreven door: op donderdag 24 juni 2021

    In IIS kan je met behulp van rewrites de aanvragen naar je site omleiden of aanpassen. Deze rewrites wil je vaak niet altijd toepassen, maar voorwaardelijk. Hiervoor kan je condities gebruiken. Bij he ...

    Bekijk het artikel »
  • Debuggen met breakpoints in Visual Studio

    Geschreven door: op maandag 21 juni 2021

    Het ontwikkelen van applicaties bestaat voor een groot gedeelte uit het oplossen van problemen en het vinden en verhelpen van vreemde bugs. Er zijn hier veel hulpmiddelen en strategieën voor. Je kan f ...

    Bekijk het artikel »
Bel 072 5345 888
Open Nieuwsbrief Inschrijving Footer

E-book

Zo wordt uw website een lead generator:
In 3 stappen uw website van visitekaartje naar salesfunnel

Download het E-book Â»

E-book

Zo wordt uw website een lead generator 
In 3 stappen uw website van visitekaartje naar salesfunnel

Download het E-book â€º