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

  • Interfaces om mee te testen

    Geschreven door: op zondag 24 mei 2020

    Interfaces om te testenIn object geörienteerde programmeertalen kan je gebruikmaken van interfaces. Interfaces zijn een hulpmiddel voor ontwikkelaars. Ze geven de ontwikkelaar aan wat een bepaalde kl ...

    Bekijk het artikel »
  • Het gebruik van await in C#

    Geschreven door: op zaterdag 28 maart 2020

    Binnen applicaties kunnen soms functies voorkomen die lang nodig hebben om te voltooien. Denk bijvoorbeeld aan een call naar een API van een derde partij die veel data teruggeeft die verwerkt moet wor ...

    Bekijk het artikel »
  • Het gebruik van de SemaphoreSlim class in C#

    Geschreven door: op zaterdag 28 maart 2020

    Webapplicaties kunnen meerdere calls vanuit meerdere plekken verwerken zonder dat deze requests op elkaar moeten wachten. Maar soms heb je te maken met een usecase waar je helemaal niet wilt dat funct ...

    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