In SQL kolomgebaseerde gegevens omzetten naar rijen via UNPIVOT

Geschreven door: op woensdag 25 oktober 2017

Leestijd:

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

  • SQL: Geclusterde en niet-geclusterde index

    Geschreven door: op zondag 30 juni 2019

    Het kan soms voorkomen dat het veel tijd kost voordat een bepaalde query resultaten teruggeeft. Dit kan liggen aan een scala aan problemen, zoals het gebruik van veel joins. Wat de query kan helpen ve ...

    Bekijk het artikel »
  • Hoe werkt OAuth 2.0

    Geschreven door: op zondag 30 juni 2019

    In de huidige samenleving is iedereen bijna altijd online, zo ook de applicaties waarvan de mensen gebruik maken. Als je jouw applicatie niet goed afschermt, kan dit allemaal veiligheidsrisico’s met z ...

    Bekijk het artikel »
  • OAuth 2.0: JWT token en claims

    Geschreven door: op zondag 30 juni 2019

    In mijn vorige blog heb ik uitgelegd hoe OAuth 2.0 ervoor kan zorgen dat derde partijen op een veilige manier gebruik kunnen maken van jouw applicatie met behulp van tokens. In deze blog gaan we wat m ...

    Bekijk het artikel »
Bel 072 5345 888
Meer dan 40 bedrijven vertrouwen op ons
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