In SQL kolomgebaseerde gegevens omzetten naar rijen via UNPIVOT header image

In SQL kolomgebaseerde gegevens omzetten naar rijen via UNPIVOT

woensdag 25 oktober 2017 ·Leestijd: 2 minuten
contacteer auteur:

Met UNPIVOT kan je kolomgebaseerde gegevens, zoals in een Excel-bestand, omzetten naar rijen, waarmee je de data gemakkelijker kunt verwerken in SQL.

In SQL maak je vaak gebruik van waardes die als rijen zijn opgeslagen, maar die je graag als in kolommen zou willen plaatsen. Een voorbeeld hiervan is data uit een key/value-tabel (sleutel/waarde) ophalen en wegschrijven naar een Excel-bestand.

Het omgekeerde kan ook: Een set waarin waardes in kolommen staan die je wilt plaatsen in rijen.

Een voorbeeld is wanneer je via BULK INSERT een Excel-bestand importeert en de data die daarin staat, als key/value wilt aanspreken. Stel dat deze Excel bestaat uit de volgende kolommen en rijen:

excel-1.jpg

Deze gegevens kan je inladen via BULK INSERT en het resultaat is dan een tabel die dezelfde opbouw heeft als het Excel-bestand:


SELECT * INTO _importProducten from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\dev\Producten.xlsx;HDR=YES;IMEX=1;', 'SELECT * FROM [Blad1$]');

SELECT * FROM _importProducten;

sql-1.jpg

Het is nu mogelijk deze data te gebruiken door te verwijzen naar TabelNaam.Kolom:


SELECT DISTINCT P.[KLEUR] FROM _importProducten AS P;

sql-2.jpg

Als er echter een kolom bij komt of je wilt deze data dynamischer gebruiken, dan is het handig als de kolomnamen in één veld staan en de waardes in een andere. Hiervoor kan je UNPIVOT  in SQL gebruiken.

UNPIVOT werkt als volgt: Je geeft een brontabel (of brondata) op, je geeft aan welke kolommen je wilt tonen in je nieuwe tabel en als laatste geef je ook nog op in de UNPIVOT-clausule wat de naam is van de nieuwe key- en -value-kolommen, die terugkomen in de eerste SELECT:


SELECT
       ID,
       KeyNaam,
       ValueNaam
FROM

       (
             SELECT ID, KLEUR, MAAT FROM _importProducten
       ) AS BRON_TABEL

       UNPIVOT (
             ValueNaam For KeyNaam IN (KLEUR, MAAT)
       ) AS UNPIVOT_TABEL

sql-3.jpg

Deze nieuwe tabel zou je via INSERT INTO in een nieuwe (tijdelijke) tabel kunnen plaatsen en gebruiken om een je gegevens in de database bij te werken.

In bovenstaande voorbeeld worden de kolommen statisch aangegeven (KLEUR en MAAT), maar als je een Excel-bestand importeert, weet je vaak niet welke kolommen beschikbaar zijn. In die gevallen kan je gebruik maken van dynamische queries.

Het omgekeerde van UNPIVOT is PIVOT. Deze functie gebruik je om rij-gebaseerde gegevens om te zetten naar kolommen, bijvoorbeeld om data overzichtelijk te presenteren of te exporteren naar Excel.

Zie verder: Using PIVOT and UNPIVOT.


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 â€º