Rijen naar kolommen via PIVOT header image

Rijen naar kolommen via PIVOT

donderdag 26 oktober 2017 ·Leestijd: 3 minuten
contacteer auteur:

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

B2B E-commerce… Wakker worden!
Geschreven door
op dinsdag 28 juni 2022
Veel B2B-bedrijven in blijven hangen in; nauwelijks vindbare, verouderde B2B e-commerce websites met minimale conversiemogelijkheden en een betreurenswaardige gebruikerservaring. Zonde!
B2B Leadgeneratie: Alles wat je altijd al wilde weten!
Geschreven door
op maandag 27 juni 2022
B2B online leadgeneratie is het proces van het identificeren van de ideale klanten voor uw product of dienst, en hen vervolgens overhalen tot aankoop. Het is een essentiële activiteit voor B2B sales- en marketingteams.
Wat maakt een B2B webshop succesvol?
Geschreven door
op zondag 26 juni 2022
De opmars van de B2B webshop neemt toe. Regelmatig krijgen wij de vraag of er een groot verschil is tussen een consumentgerichte webshop en een puur B2B gedreven webshop. In deze blog leggen wij u uit hoe een B2B webshop succesvol kan zijn en worden.
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 â€º