In SQL kolomgebaseerde gegevens omzetten naar rijen via UNPIVOT

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:

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;

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

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

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

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.