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.

 


Op De Hoogte Blijven?

Online Succes realiseren is een vak, een vak wat wij verstaan en waarover we je graag vertellen. Schrijf je in voor onze maandelijkse nieuwsbrief en blijf op de hoogte van trends, thema’s en succesverhalen.

Aanhef

Andere blogartikelen

Bel 072 5345 888
Meer dan 40 bedrijven vertrouwen op ons
Allrig is de alles in een leverancier binnen de energie-industrie
Aliancys is een toonaangevend wereldwijd bedrijf actief in de verkoop van kwaliteitsharsen
ERIKS is een toonaangevende en innovatieve leverancier aan de procesindustrie en aan machinebouwers, die zowel de rol van specialist als die van brede MRO-leverancier vervult
Industrieel dienstverlener Heinen & Hopman Engineering uit Bunschoten is dé wereldwijde specialist op het gebied van klimaatbeheersing
Handicare is een internationale organisatie die ouderen helpt om hun dagelijks leven gemakkelijker te maken door het produceren van hoogwaardige trapliften
Onze Middelen en Technologieën
microsoft silver partner
Adobe partner
fd gazellen 2017
Google analytics
Google adwords
TelefoonE-mail