Ez a cikk teljes áttekintést nyújt a PIVOT és UNPIVOT operátorok használatáról az SQL Serverben. A PIVOT és UNPIVOT operátorok hasonlóak azokhoz a relációs operátorokhoz, amelyek lehetővé teszik a táblázat értékű kifejezést egy másik táblává alakítva . Mindkét operátor többdimenziós jelentéseket készít, amelyek nagy mennyiségű adat gyors kombinálását és összehasonlítását segítik elő.
Használhatjuk a PIVOT operátor amikor tábla értékű kifejezéseket kell átalakítanunk. Felosztja a egyedi értékek egy oszlopból több oszlopba a végeredményben. Az is aggregátumok a végeredményben szükséges többi oszlopértéket. UNPIVOT operátor táblázat értékű kifejezés oszlopaiból származó adatokat oszlopértékekké alakítja, ami a PIVOT inverze.
Értsük meg az alábbi egyszerű diagram segítségével:
Az ábra bal oldalán láthatjuk a eredeti adatkészlet , amelynek három oszlopa van: év, régió, és Értékesítés . Ezután a jobb oldalon láthatjuk a PIVOT táblázatot, amely a Régió (sorok) északra és délre (oszlopok) . A sorok oszlopokká alakítása után tehetünk egy az Értékesítés oszlop értékeinek összesítése a PIVOT tábla oszlopai és sorai közötti metszéspontokhoz.
Először hozzunk létre egy nevű táblát pivot_demo a PIVOT és UNPIVOT operátorok bemutatására. A következő utasítás új táblát hoz létre a megadott adatbázisunkban:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
Ezután illesszen be néhány adatot ebbe a táblázatba az alábbiak szerint:
INSERT INTO pivot_demo VALUES ('North', 2010, 72500), ('South', 2010, 60500), ('South', 2010, 52000), ('North', 2011, 45000), ('South', 2011, 82500), ('North', 2011, 35600), ('South', 2012, 32500), ('North', 2010, 20500);
Az adatokat a SELECT utasítással tudjuk ellenőrizni. Az alábbi kimenetet kapjuk:
PIVOT kezelő
Ez az operátor a táblázat értékű kifejezések elforgatására szolgál. Először az SQL Server 2005-ös verziójában mutatták be. Az adatokat sorokból oszlopokká alakítja. Egy oszlop egyedi értékeit több oszlopra bontja, majd a végeredményben összesíti a fennmaradó oszlopértékeket.
PIVOT tábla létrehozásához a következő lépéseket kell követnünk:
- Válassza ki az alapadatkészletet az elforgatáshoz.
- Hozzon létre ideiglenes eredményeket származtatott tábla vagy CTE (közös táblakifejezés) segítségével.
- Használja a PIVOT operátort.
Szintaxis
A következő szintaxis szemlélteti a PIVOT használatát az SQL Serverben:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Ha megtörjük ezt a szkriptet, láthatjuk, hogy két különálló része van. Az első szakasz a fő tábla adatait választja ki, a második szakasz pedig meghatározza a PIVOT tábla felépítésének módját. A második rész néhány speciális kulcsszót is tartalmaz, például SUM, FOR és IN. Lássuk ezeknek a kulcsszavaknak a jelentését a PIVOT operátorban.
ÖSSZEG
Ez az operátor hozzászokott aggregálja az értékeket a megadott oszlopból a PIVOT táblában használandó. Használnunk kell a PIVOT operátorral, hogy megkapjuk az összesített oszlopmegjelenítéseket az értékek szakaszokhoz.
FOR Kulcsszó
Ez a kulcsszó a PIVOT tábla utasításához használatos utasítsa a PIVOT operátort melyik oszlopon kell alkalmazni a PIVOT funkciót. Alapvetően az oszlopneveket jelzi, amelyek sorokból oszlopokká alakulnak.
IN Kulcsszó
Ez a kulcsszó felsorolja az összes egyedi értéket a PIVOT oszlopból, hogy a PIVOT tábla oszlopaiként jelenjen meg.
Példa
Értsük meg különféle példák segítségével.
1. A következő utasítás először az Év, Észak és Dél oszlopot választja ki az elforgatás alapadataként. Ezután hozzon létre egy ideiglenes eredményt a származtatott táblázat segítségével, és végül alkalmazza a PIVOT operátort a végső kimenet létrehozásához. Ez a kimenet is növekvő évre van rendelve.
SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS Tab2 ORDER BY Tab2.Year
Ennek az utasításnak a végrehajtása az alábbi kimenetet eredményezi. Itt láthatjuk a az északi és déli régió eladásainak számított összege az év értékeinek megfelelően .
2. Ez egy másik példa, ahol kiszámítjuk az egyes évek eladásainak összegét a régióértékeknek megfelelően:
SELECT Region, 2010, 2011, 2012 FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN (2010, 2011, 2012)) AS Tab2 ORDER BY Tab2.Region;
Ennek a nyilatkozatnak a végrehajtása az hibát produkál mert nem tudjuk közvetlenül oszlopnévként megadni a numerikus értéket.
Az SQL Server azonban lehetővé teszi, hogy elkerüljük ezt a problémát, ha minden egész érték előtt zárójeleket használunk. A frissített nyilatkozat a következő kódrészletben látható:
SELECT Region, [2010], [2011], [2012] FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN ([2010], [2011], [2012])) AS Tab2 ORDER BY Tab2.Region;
Ez a kimutatás sikeresen lefutott, és a régióértékeknek megfelelően minden évre kiszámított értékesítési összeget jelenít meg:
3. A PIVOT tábla beszerzésének előző példája akkor hasznos, ha tisztában vagyunk az összes lehetséges PIVOT oszlopértékkel. De tegyük fel, hogy a következő évben az oszlopok száma megnövekszik. Az előző példát figyelembe véve a 2010-es, 2011-es és 2012-es év PIVOT oszlopként szerepel. Nincs azonban garancia arra, hogy ezek az oszlopok a jövőben nem változnak. Mi történik, ha 2013-ból vagy 2014-ből, vagy esetleg még több adatunk van? Ilyen esetekben használnunk kell dinamikus PIVOT tábla lekérdezések a probléma megoldásához.
A dinamikus PIVOT-tábla lekérdezés a teljes PIVOT-szkriptet egy tárolt eljárásba foglalja. Ez az eljárás állítható lehetőségeket biztosít, lehetővé téve, hogy néhány paraméterezett érték megváltoztatásával módosítsuk követelményeinket.
A következő SQL-kód elmagyarázza a dinamikus PIVOT tábla működését. Ebben a szkriptben először lekértük az összes különálló értéket a PIVOT oszlopból, majd írtunk egy SQL utasítást a PIVOT lekérdezéssel való végrehajtáshoz futás közben. Lássuk a kimenetet a szkript végrehajtása után:
hálózat és internet
CREATE PROCEDURE DynamicPivotTable @PivotColumn NVARCHAR(255), @PivotList NVARCHAR(255) AS BEGIN DECLARE @Query NVARCHAR(MAX); SET @Query = N' SELECT * FROM (SELECT [Region], [Year], [Sales] FROM pivot_demo) AS tab1 PIVOT (SUM([Sales]) FOR ['+@Pivot_Column+'] IN ('+@Pivot_List+')) AS PivotTable'; EXEC(@Query) END
Ebben a szkriptben két paraméterezett változót hoztunk létre. Leírása az alábbiakban található:
@PivotColumn : Ez a változó az oszlop nevét abból az eredeti táblából veszi, amelyen a PIVOT tábla létrejött. Például , itt a „Régió” oszlop az oszlopokban elérhető összes régiót megjeleníti.
@PivotList : Ez a változó azt az oszloplistát veszi fel, amelyet kimeneti oszlopként szeretnénk megjeleníteni a PIVOT táblában.
Dinamikus tárolt eljárás végrehajtása
A dinamikus tárolt eljárás sikeres létrehozása után készen állunk a végrehajtásra. A következő utasítás a dinamikus tárolt eljárás meghívására szolgál a PIVOT tábla futás közbeni megjelenítéséhez:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Itt most megadtuk az oszlop nevét ' Vidék ' mint első paraméter, és a PIVOT oszloplista második paraméterként. A szkript végrehajtása a következő kimenetet jeleníti meg:
Mostantól a jövőben további oszlopokat adhatunk hozzá futás közben a PIVOT tábla megjelenítéséhez, ami az első két példában nem lehetséges.
UNPIVOT operátor
Ez az SQL Server PIVOT operátorának fordított módszere. Ez az operátor végrehajtja a a PIVOT ellentétes működése az adatok oszlopokból sorokká konvertálásával. Az UNPIVOT operátor a PIVOT táblát is a normál táblázatba forgatja. Először az SQL Server 2005-ös verziójában mutatták be.
Szintaxis
A következő szintaxis az SQL Server UNPIVOT-ját szemlélteti:
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Példa
Példák segítségével értsük meg, hogyan lehet a PIVOT művelet UNPIVOT-ját. Először létrehozunk egy eredeti táblázat és PIVOT tábla majd az UNPIVOT operátort alkalmazta ezen a táblázaton.
A következő kódrészlet először deklarál egy ideiglenes @Tab táblaváltozót:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
Ezután az alábbiak szerint beillesztjük az értékeket ebbe a táblázatba:
INSERT INTO @Tab SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ORDER BY PivotTable.Year
Most végre tudjuk hajtani az UNPIVOT műveletet az alábbi utasítással:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
A kódrészlet végrehajtása a következő kimenetet adja vissza:
Az alábbi kódrészlet egy másik példa arra, hogy először PIVOT műveletet, majd UNPIVOT műveletet hajt végre ugyanazon a táblán egyetlen lekérdezésben:
SELECT Region, Year, Sales FROM ( SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ) P --Perform UNPIVOT Operation UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
A kódrészlet végrehajtása ugyanazt a kimenetet jeleníti meg:
MEGJEGYZÉS: Az UNPIVOT folyamat a PIVOT eljárás fordított művelete, de nem pontos megfordítása. Mivel a sorok összevonásra kerültek, amikor a PIVOT kiszámítja az aggregátumot, és sok sort egyetlen sorba egyesített az eredményben, ezért az UNPIVOT művelet nem tudja az eredetihez hasonlóvá tenni a táblázatot. Ha azonban a PIVOT operátor nem von össze sok sort egyetlen sorba, akkor az UNPIVOT operátor megkaphatja az eredeti táblázatot a PIVOT kimenetből.
Következtetés
Ez a cikk teljes áttekintést ad az SQL Server PIVOT és UNPIVOT operátorairól, és egy táblakifejezést egy másikra konvertál. Soha nem szabad elfelejteni, hogy az UNPIVOT a PIVOT fordított művelete, de ez nem a PIVOT eredmény pontos inverze.