A PIVOT és UNPIVOT operátorok használata adatok átalakítására SQL-ben

Az adatok ereje abban rejlik, ahogyan értelmezzük és felhasználjuk őket. Az SQL adatbázisok a modern üzleti intelligencia gerincét képezik, és gyakran előfordul, hogy az adatok megjelenítési formája kulcsfontosságú az elemzéshez vagy a riportoláshoz. Előfordult már, hogy egy táblázatos riportot kellett készítenie, ahol a sorokban lévő egyedi értékek oszlopfejlécekké váltak? Vagy épp ellenkezőleg, egy „széles” táblából kellett „hosszú” formátumot generálnia az aggregált elemzésekhez? Nos, pontosan erre szolgálnak az SQL PIVOT és UNPIVOT operátorok. Ezek a hatékony eszközök lehetővé teszik számunkra, hogy radikálisan átalakítsuk az adatok szerkezetét, megkönnyítve ezzel a betekintést és a további feldolgozást.

Ebben a cikkben mélyrehatóan megvizsgáljuk, hogyan működik a PIVOT és az UNPIVOT, mikor érdemes használni őket, bemutatjuk szintaxisukat részletes példákkal, és kitérünk a legjobb gyakorlatokra, valamint az alternatív megoldásokra. Készüljön fel, hogy bővítse SQL eszköztárát, és új szintre emelje az adatokkal való munkát!

Miért van szükség adatok átalakítására?

Az adatok gyakran egy „normált” vagy „hosszú” formában tárolódnak az adatbázisokban, ami hatékony a tárolás és az integritás szempontjából. Például egy értékesítési táblában minden egyes eladás külön sorban szerepelhet, a termék, a hónap és az eladott mennyiség oszlopokkal. Ez rendben is van az adatbevitelhez és tranzakciókhoz, de mi van akkor, ha egy vezetői riportot kell készíteni, amely hónapok szerinti lebontásban mutatja be a termékek összesített eladásait, ahol a hónapok oszlopokként szerepelnek? Ekkor az adatok vizuálisan sokkal érthetőbbek lennének egy „széles” formátumban. Ugyanígy, ha egy külső rendszer „széles” formátumban tárolja az adatokat (pl. évszázadok szerinti bontásban külön oszlopokban), de nekünk egy egységes, „hosszú” formátumra van szükségünk elemzéshez, akkor is beavatkozásra van szükség.

Itt jönnek képbe az átalakító operátorok. A PIVOT sorokból oszlopokat készít, míg az UNPIVOT oszlopokból sorokat generál. Mindkettő elengedhetetlen eszköz a modern adatelemzésben és riportkészítésben.

A PIVOT operátor: Sorokból oszlopokat

A PIVOT operátor egy rendkívül hasznos funkció, amely lehetővé teszi, hogy egy táblában található egyedi sorértékeket új oszlopokká alakítsunk át. Ezzel egy időben, a megadott aggregációs függvény segítségével összefoglalja a megfelelő adatokat. Gondoljon rá úgy, mint egy dinamikus kereszttáblázatra vagy kimutatásra, amelyet közvetlenül az SQL lekérdezésben hoz létre.

A PIVOT működése és szintaxisa

A PIVOT operátor a következő főbb részekből áll:

Egy forrástábla vagy egy al-lekérdezés, amely tartalmazza az átalakítandó adatokat.
Egy aggregációs függvény (pl. SUM, COUNT, AVG, MAX, MIN), amely meghatározza, hogyan összegződjenek az adatok az új oszlopokban.
Egy FOR záradék, amely meghatározza, mely oszlop értékei válnak új oszlopfejlécekké.
Egy IN záradék, amely felsorolja azokat az egyedi értékeket, amelyek az új oszlopok nevévé válnak. Ezeknek az értékeknek expliciten meg kell jelenniük a lekérdezésben.

Általános szintaxis (T-SQL dialektusban):

SELECT <nem-pivotált-oszlopok>,
       [pivot_oszlop_1],
       [pivot_oszlop_2],
       ...
       [pivot_oszlop_n]
FROM
    (<az_alap_adatokat_generáló_lekérdezés>) AS ForrasTabla
PIVOT
(
    <aggregációs_függvény>(<érték_oszlop>)
    FOR <pivot_oszlop> IN ([pivot_oszlop_1], [pivot_oszlop_2], ... [pivot_oszlop_n])
) AS PivotTabla;

ForrasTabla: Az az adathalmaz, amelyből pivotálni szeretnénk. Ez lehet egy tábla neve, egy nézet, vagy egy Common Table Expression (CTE).
<aggregációs_függvény>(<érték_oszlop>): Ez a rész adja meg, mely oszlopon kell végrehajtani az aggregációt (pl. SUM(Mennyiseg)).
FOR <pivot_oszlop> IN (...): Itt adja meg, melyik oszlopból származó értékek válnak oszlopfejlécekké, és konkrétan mely értékek. A zárójelek közötti lista ([pivot_oszlop_1], [pivot_oszlop_2], ...) lesznek az új oszlopok nevei.

PIVOT példa: Havi értékesítések termékek szerint

Képzeljen el egy Ertekesites táblát az alábbi adatokkal:

CREATE TABLE Ertekesites (
    TermekID INT,
    Honap VARCHAR(3),
    Osszeg DECIMAL(10, 2)
);

INSERT INTO Ertekesites (TermekID, Honap, Osszeg) VALUES
(1, 'Jan', 100),
(1, 'Feb', 150),
(1, 'Mar', 120),
(2, 'Jan', 200),
(2, 'Feb', 180),
(2, 'Mar', 210),
(1, 'Jan', 50); -- Egy másik eladás az 1-es termékre januárban

Célunk, hogy a termékazonosítók szerint csoportosítva, oszlopokban lássuk a januári, februári és márciusi összesített értékesítéseket:

SELECT TermekID, Jan, Feb, Mar
FROM
(
    SELECT TermekID, Honap, Osszeg
    FROM Ertekesites
) AS ForrasTabla
PIVOT
(
    SUM(Osszeg)
    FOR Honap IN ([Jan], [Feb], [Mar])
) AS PivotTabla;

Az eredmény valami hasonló lesz:

TermekID | Jan   | Feb   | Mar
---------|-------|-------|-------
1        | 150.00| 150.00| 120.00
2        | 200.00| 180.00| 210.00

Láthatja, hogy az 1-es termék januári értékesítései (100 + 50) összeadódtak 150-re. A Honap oszlop egyedi értékei (Jan, Feb, Mar) új oszlopfejlécekké váltak, és az Osszeg oszlop értékei összegződtek a megfelelő hónapok alatt.

PIVOT korlátok és megfontolások

Statikus oszlopok: A PIVOT operátor legnagyobb korlátja, hogy az új oszlopneveknek (az IN záradékban szereplő értékeknek) ismerteknek kell lenniük a lekérdezés írásakor. Ez azt jelenti, hogy ha a hónapok száma dinamikusan változik (pl. havonta új hónapok jönnek hozzá), akkor a lekérdezést módosítani kell.
NULL értékek: Ha egy adott csoportra (pl. TermekID) vonatkozóan nincs érték az egyik pivotált oszlophoz (pl. egy termék nem volt eladva egy adott hónapban), akkor a PIVOT eredményben az a cella NULL értéket fog tartalmazni.
Teljesítmény: Nagy adathalmazok esetén a PIVOT operáció teljesítményigényes lehet. Fontos a forrás lekérdezés optimalizálása.
Adattípusok: Az aggregált oszlopnak kompatibilis adattípusúnak kell lennie.

Az UNPIVOT operátor: Oszlopokból sorokat

Az UNPIVOT operátor a PIVOT pontos ellentéte. A „széles” formátumú adatokból „hosszú” formátumú adatokat hoz létre, vagyis oszlopokat alakít át sorokká. Ez különösen hasznos, ha normalizálni kell az adatokat további elemzésekhez, vagy ha egy külső rendszer egy „hosszú” formátumú adatkészletet vár.

Az UNPIVOT működése és szintaxisa

Az UNPIVOT szintaxisa szintén tartalmaz egy forrástáblát vagy lekérdezést, de az aggregációs függvények helyett itt azt definiáljuk, mely oszlopok válnak értékoszloppá, és melyek az eredeti oszlopnevek tárolására szolgáló oszloppá.

Általános szintaxis (T-SQL dialektusban):

SELECT <nem-unpivotált-oszlopok>,
       <név_oszlop>,
       <érték_oszlop>
FROM
    (<az_alap_adatokat_generáló_lekérdezés>) AS ForrasTabla
UNPIVOT
(
    <érték_oszlop> FOR <név_oszlop> IN ([unpivotálandó_oszlop_1], [unpivotálandó_oszlop_2], ... [unpivotálandó_oszlop_n])
) AS UnpivotTabla;

ForrasTabla: Az az adathalmaz, amelyből unpivotálni szeretnénk. Ez gyakran egy korábban pivotált tábla vagy annak eredménye.
<érték_oszlop> FOR <név_oszlop>: Itt adja meg, hogy az eredeti oszlopok értékei melyik új oszlopba kerüljenek (<érték_oszlop>), és az eredeti oszlopok nevei melyik új oszlopba (<név_oszlop>).
IN ([...]): Felsorolja azokat az oszlopokat a ForrasTabla-ból, amelyeket sorokká szeretnénk alakítani.

UNPIVOT példa: Vissza a havi értékesítésekhez

Vegyük alapul az előző PIVOT lekérdezés eredményét. Tegyük fel, hogy ezt az eredményt egy CTE-ben (Common Table Expression) tároljuk, vagy egy ideiglenes táblában:

WITH PivotaltErtekesites AS (
    SELECT 1 AS TermekID, 150.00 AS Jan, 150.00 AS Feb, 120.00 AS Mar UNION ALL
    SELECT 2 AS TermekID, 200.00 AS Jan, 180.00 AS Feb, 210.00 AS Mar
)
SELECT TermekID, Honap, Osszeg
FROM PivotaltErtekesites
UNPIVOT
(
    Osszeg FOR Honap IN ([Jan], [Feb], [Mar])
) AS UnpivotTabla;

Az eredmény visszaáll a „hosszú” formátumra:

TermekID | Honap | Osszeg
---------|-------|-------
1        | Jan   | 150.00
1        | Feb   | 150.00
1        | Mar   | 120.00
2        | Jan   | 200.00
2        | Feb   | 180.00
2        | Mar   | 210.00

Láthatja, hogy a Jan, Feb és Mar oszlopok értékei a Osszeg oszlopba kerültek, míg az oszlopnevek (Jan, Feb, Mar) a Honap oszlopba. Így kaptuk vissza az eredetihez hasonló, de aggregált, sor alapú adatokat.

UNPIVOT korlátok és megfontolások

NULL értékek kezelése: Az UNPIVOT operátor alapértelmezetten figyelmen kívül hagyja azokat a sorokat, ahol az unpivotálandó oszlop értéke NULL. Ez egy fontos különbség más módszerekhez képest, és lehet előny vagy hátrány a felhasználási esettől függően.
Adattípusok: Az IN záradékban felsorolt összes oszlopnak kompatibilis adattípusúnak kell lennie. Ha eltérő típusú oszlopokat próbál unpivotálni, hibaüzenetet kaphat. Megoldásként explicit adattípus-konverziót (CAST vagy CONVERT) kell alkalmazni az UNPIVOT előtt.
Teljesítmény: Nagy adathalmazok esetén az UNPIVOT is okozhat teljesítményproblémákat, bár általában kevésbé erőforrás-igényes, mint a PIVOT, mivel nincs benne aggregáció.

Mikor használjuk a PIVOT és UNPIVOT operátorokat?

PIVOT használati esetek:

Riportkészítés: Kereszttáblázatos riportok, ahol a dimenziók (pl. hónapok, régiók) oszlopokként jelennek meg. Ez sokkal olvashatóbbá teszi az adatokat a végfelhasználók számára.
Adatelemzés: Bizonyos elemzőeszközök vagy statisztikai programok „széles” formátumú bemenetet várnak.
Adatvizualizáció: Amikor az adatok grafikus megjelenítéséhez (pl. oszlopdiagramok) előnyösebb, ha a kategóriák oszlopokban helyezkednek el.
Összegzés: Adatok gyors összegzése egy adott dimenzió mentén.

UNPIVOT használati esetek:

Adatnormalizálás: Ha az adatok „széles” formában érkeznek (pl. Excelből importálva, ahol minden év külön oszlop), és normalizált, „hosszú” formátumra van szükség a hatékonyabb tároláshoz vagy lekérdezéshez.
Adatraktár (Data Warehouse) ETL folyamatok: Adatok átalakítása a forrásrendszerből a cél adatraktárba való betöltés előtt.
Egyszerűsített elemzés: Ha az elemzés során a különböző kategóriákra (pl. hónapok) egységesen szeretne szűrni vagy aggregálni, sokkal egyszerűbb egy WHERE Honap = 'Jan' feltétellel, mint több oszlopot vizsgálni.
Külső rendszerek kompatibilitása: Bizonyos alkalmazások vagy API-k csak „hosszú” formátumú adatokat fogadnak el.

Alternatívák és legjobb gyakorlatok

Bár a PIVOT és UNPIVOT operátorok nagyon hatékonyak, vannak alternatívák, különösen akkor, ha a dinamikus oszlopnevek kezelése vagy a platformfüggetlenség kulcsfontosságú.

PIVOT alternatíva: Feltételes aggregáció (CASE WHEN)

Ez a módszer sokkal rugalmasabb, mivel lehetővé teszi a dinamikus oszlopnevek kezelését (bár magát a lekérdezést még mindig generálni kell, ha teljesen dinamikusak az oszlopok), és általában jobban portolható a különböző SQL dialektusok között.

SELECT TermekID,
       SUM(CASE WHEN Honap = 'Jan' THEN Osszeg ELSE 0 END) AS Jan,
       SUM(CASE WHEN Honap = 'Feb' THEN Osszeg ELSE 0 END) AS Feb,
       SUM(CASE WHEN Honap = 'Mar' THEN Osszeg ELSE 0 END) AS Mar
FROM Ertekesites
GROUP BY TermekID;

Ez a lekérdezés pontosan ugyanazt az eredményt adja, mint az előző PIVOT példa. Előnye, hogy bármely SQL szerverrel működik, és a logika átláthatóbb lehet a bonyolultabb aggregációk esetén.

UNPIVOT alternatíva: UNION ALL

Az UNPIVOT funkciót helyettesítheti több SELECT utasítás UNION ALL operátorral történő egyesítésével. Ez azonban nagyon verbózzá válhat, ha sok oszlopot kell unpivotálni.

WITH PivotaltErtekesites AS (
    SELECT 1 AS TermekID, 150.00 AS Jan, 150.00 AS Feb, 120.00 AS Mar UNION ALL
    SELECT 2 AS TermekID, 200.00 AS Jan, 180.00 AS Feb, 210.00 AS Mar
)
SELECT TermekID, 'Jan' AS Honap, Jan AS Osszeg FROM PivotaltErtekesites UNION ALL
SELECT TermekID, 'Feb' AS Honap, Feb AS Osszeg FROM PivotaltErtekesites UNION ALL
SELECT TermekID, 'Mar' AS Honap, Mar AS Osszeg FROM PivotaltErtekesites;

Ez a megközelítés is ugyanazt az eredményt adja, de ahogy látható, minden unpivotálandó oszlophoz külön sort kell írni. Kisebb számú oszlop esetén ez még elfogadható lehet, de nagyobb adathalmazoknál az UNPIVOT sokkal elegánsabb.

Legjobb gyakorlatok a PIVOT és UNPIVOT használatához:

Alapos tervezés: Mielőtt elkezdené írni a lekérdezést, gondolja át pontosan, milyen formátumban szeretné látni az adatokat. Mely oszlopok lesznek a sorok, melyek az oszlopok, és melyik oszlopot kell aggregálni (PIVOT esetén)?
Kisebb adathalmazzal teszteljen: Kezdje egy szűkített adathalmazzal, hogy megértse a működést, mielőtt éles adatokra alkalmazza.
Adattípusok konverziója: Különösen az UNPIVOT esetén ellenőrizze és szükség esetén konvertálja az unpivotálandó oszlopok adattípusait, hogy kompatibilisek legyenek.
Dinamikus oszlopok: Ha a pivotálandó oszlopok száma előre nem ismert, és gyakran változik, érdemesebb lehet dinamikus SQL-t generálni az alkalmazás szintjén, vagy a CASE WHEN módszert használni, ha a lehetséges oszlopok száma korlátozott.
Teljesítményfigyelés: Nagyméretű táblákon végzett PIVOT/UNPIVOT műveletek esetén monitorozza a lekérdezés teljesítményét, és optimalizálja az al-lekérdezéseket.

Összefoglalás

A PIVOT és UNPIVOT operátorok rendkívül erőteljes eszközök az SQL-ben az adatok átalakítására. A PIVOT sorokból oszlopokat készít aggregációval, míg az UNPIVOT oszlopokat alakít át sorokká. Ezek az operátorok kulcsfontosságúak lehetnek a riportkészítés, adatelemzés, adatnormalizálás és adatbázisok közötti integráció során.

Bár a statikus oszlopnevek kezelése némi korlátot jelenthet, a CASE WHEN alapú feltételes aggregáció rugalmas alternatívát kínál. A megfelelő eszköz kiválasztása a konkrét feladattól és az adatbázis-kezelő rendszertől függ.

Az adatok rugalmas manipulálásának képessége az egyik legfontosabb készség egy SQL fejlesztő vagy adatelemző számára. A PIVOT és UNPIVOT operátorok elsajátításával olyan komplex adattranszformációkat hajthat végre, amelyekkel a hagyományos SQL módszerekkel sokkal nehezebben vagy egyáltalán nem boldogulna. Kezdjen el kísérletezni velük saját adataival, és hamarosan rájön, milyen sokoldalúan alkalmazhatók!