Rijen naar kolommen via PIVOT

Geschreven door: op donderdag 26 oktober 2017

Leestijd:

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

  • Waarom developers het zo leuk vinden bij ons

    Geschreven door: op woensdag 13 november 2019

    Bij ons op kantoor werkt een team van betrokken specialisten, waaronder 4 backend developers en 4 UX developers. Zij zorgen voor een belangrijk fundament binnen dit bedrijf. Een goede reden om te zorg ...

    Bekijk het artikel »
  • Beachvolleyballen voor het goede doel!

    Geschreven door: op vrijdag 25 oktober 2019

    Afgelopen vrijdag 11 oktober  hebben diverse betrokken specialisten uit ons team de handen ineen geslagen met een van onze opdrachtgevers. Samen met De Monchy International B.V. hebben we meegeda ...

    Bekijk het artikel »
  • Het gebruik van een Photoslider

    Geschreven door: op maandag 7 oktober 2019

    Ze zijn er in alle soorten en maten en staan meestal bovenaan een websitepagina. Dan heb ik het natuurlijk over photosliders. Zo bent u vast bekend met de traditionele foto die langzaam vervaagd en wa ...

    Bekijk het artikel »
Bel 072 5345 888
Onze Middelen en Technologieën
microsoft silver partner
Adobe partner
fd-gazellen-2018.jpg
Google analytics
partners-logo.jpg
Op de hoogte blijven?

Meld u aan voor de gratis nieuwsbrief om op de hoogte te blijven van onze activiteiten