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.


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