Kimutatást tartalmazó lekérdezések írása és eredményhalmazok feloldása
Az SQL Server kimutatásával elforgathatja az adatok megjelenítési módját a sorokon alapuló tájolástól az oszlopokon alapuló tájolásig. A kimutatáskor egy oszlop értékeit összevonja egy különböző értékek listájával, majd a listát oszlopfejlécekként vetíti ki. Ez általában magában foglalja az új oszlopok oszlopértékeinek összesítését.
Az alábbi részleges forrásadatok például a Kategória és az Orderyear ismétlődő értékeit, valamint a Qty értékeit sorolják fel egy Kategória/Rendelésév pár minden példányához:
| Kategória | Mennyiség | Rendelési év |
|---|---|---|
| Tejtermékek | 12 | 2019 |
| Magok/gabonafélék | 10 | 2019 |
| Tejtermékek | 5 | 2019 |
| Tengeri | 2 | 2020 |
| Édesség | 36 | 2020 |
| Fűszerek | 35 | 2020 |
| Édesség | 55 | 2020 |
| Fűszerek | 16 | 2020 |
| Tejtermékek | 60 | 2020 |
| Tejtermékek | 20 | 2020 |
| Édesség | 24 | 2020 |
| ... (2155 érintett sor(ok) |
A fenti táblázat több mint 2000 sort jelöl, sok ismétlődő értékkel. Ha kategória és év szerint szeretné elemezni az eredményeket, érdemes lehet a következőképpen rendezni az értékeket, az út mentén összegezve a Qty oszlopot:
| Kategória | 2019 | 2020 | 2021 |
|---|---|---|---|
| Italok | 1842 | 3996 | 3694 |
| Fűszerek | 962 | 2895 | 1441 |
| Édesség | 1357 | 4137 | 2412 |
| Tejtermékek | 2086 | 4374 | 2689 |
| Magok/gabonafélék | 549 | 2636 | 1377 |
| Hús/baromfi | 950 | 2189 | 1060 |
| Termel | 549 | 1583 | 858 |
| Tengeri | 1286 | 3679 | 2716 |
| (8 sor érintett) |
Az eredményhalmaz most összesen nyolc sort jelent. A kimutatási folyamat során minden különálló év oszlopfejlécként lett létrehozva, a Qty oszlop értékei pedig kategóriák szerint lettek csoportosítva és összesítve.
Eredményhalmaz kimutatása a PIVOT használatával
Az eredményhalmazt a PIVOT operátorral forgathatja. A Transact-SQL PIVOT tábla operátor egy Standard kiadás LECT utasítás FROM záradékának kimenetén működik. A PIVOT használatához három elemet kell megadnia az operátornak:
- Csoportosítás: a FROM záradékban adja meg a bemeneti oszlopokat. Ezekből az oszlopokból a PIVOT határozza meg, hogy melyik oszlop(ok) lesznek használva az összesítés adatainak csoportosításához. Ez azon alapul, hogy mely oszlopokat nem használják más elemként a PIVOT operátorban.
- Szórás: a kimutatásban szereplő adatok oszlopfejléceként használandó értékek vesszővel tagolt listáját adja meg. Az értékeknek a forrásadatokban kell történnie.
- Összesítés: a csoportosított sorokon végrehajtandó összesítési függvényt (SZUM stb.) ad meg.
Emellett egy táblaaliast is hozzá kell rendelnie a KIMUTATÁS operátor eredménytáblához. Az alábbi példa a helyben lévő elemeket mutatja be:
SELECT Category, [2019],[2020],[2021]
FROM ( SELECT Category, Qty, Orderyear FROM Sales.CategoryQtyYear) AS D
PIVOT(SUM(qty) FOR orderyear IN ([2019],[2020],[2021])) AS pvt;
A fenti példában az Orderyear a szórási értékeket tartalmazó oszlop, a Qty az összesítéshez, a csoportosítás kategóriája . Az orderyear értékek elválasztójelek közé vannak zárva, amelyek azt jelzik, hogy az eredmény oszlopazonosítói.
Eredményhalmaz leválasztása a UNPIVOT használatával
Az adatok leválasztása az adatok kimutatásának logikai fordítottja. A sorok oszlopokká alakítása helyett az oszlopokat nem lehet sorokká alakítani. Ez egy olyan technika, amely akkor hasznos, ha a már elforgatott adatokat (Transact-SQL PIVOT operátorral vagy anélkül) készíti el, és egy sororientált táblázatos megjelenítésre adja vissza. Ehhez használhatja a UNPIVOT tábla operátort.
A UNPIVOT operátor használatához három elemet kell megadnia:
- A forrásoszlopokat fel kell szabadítani.
- Az új oszlop neve, amely megjeleníti a meg nem jelenített értékeket.
- Annak az oszlopnak a neve, amely a meg nem jelenített értékek nevét jeleníti meg.
Az alábbi példa a 2019-et, a 2020-ast és a 2021-et adja meg a visszavonni kívánt oszlopokként az új oszlopnévsorévés az új qty oszlopban megjelenítendő mennyiségértékek használatával.
SELECT category, qty, orderyear
FROM Sales.PivotedCategorySales
UNPIVOT(qty FOR orderyear IN([2019],[2020],[2021])) AS unpvt;
Az adatok törlésekor egy vagy több oszlop lesz definiálva a sorokká konvertálandó forrásként. Az oszlopokban lévő adatok egy vagy több új sorra vannak felosztva vagy felosztva attól függően, hogy hány oszlopot törölnek. A következő forrásadatokban három oszlop lesz leválasztva. Minden Orderyear érték egy új sorba lesz másolva, és hozzá lesz rendelve a kategóriaértékhez. A folyamat során minden NULL el lesz távolítva, és nem jön létre sor:
| Kategória | 2019 | 2020 | 2021 |
|---|---|---|---|
| Italok | 1842 | 3996 | 3694 |
| Fűszerek | 962 | 2895 | 1441 |
| Édesség | 1357 | 4137 | 2412 |
| Tejtermékek | 2086 | 374 | 2689 |
| Magok/gabonafélék | 549 | 2636 | 1377 |
| Hús/baromfi | 950 | 2189 | 1060 |
| Termel | 549 | 1583 | 858 |
| Tengeri | 1286 | 3679 | 2716 |
A Kategória és az Orderyear minden metszeténél új sor jön létre, az alábbi részleges eredményekhez hasonlóan:
| kategória | qty | orderyear |
|---|---|---|
| Italok | 1842 | 2019 |
| Italok | 3996 | 2020 |
| Italok | 3694 | 2021 |
| Fűszerek | 962 | 2019 |
| Fűszerek | 2895 | 2020 |
| Fűszerek | 1441 | 2021 |
| Édesség | 1357 | 2019 |
| Édesség | 4137 | 2020 |
| Édesség | 2412 | 2021 |
A leállítás nem állítja vissza az eredeti adatokat. A részletszintű adatok elvesztek az eredeti kimutatás összesítési folyamata során. A UNPIVOT nem tudja lefoglalni az értékeket, hogy visszatérjenek az eredeti részletértékekhez.