ebben a cikkben végigjárjuk az SQL Pivot és az SQL Unpivot operátorokat, valamint azt, hogy ezek milyen hasznosak lehetnek az SQL Server adatainak átültetéséhez. Ezenkívül megvitatjuk mind a statikus, mind a dinamikus módszereket a PIVOT és a UNPIVOT relációs operátorok használatára, amelyek felhasználhatók az összesített különálló értékek oszlop(ok) ként történő átalakítására az eredménykészletben, a pivot összes oszlopértékének megadásával a záradékban.
SQL Server Pivot Introduction
számos módja van, hogy ültesse át az adathalmaz sorokból oszlopokba és oszlopokba sorokba. Az SQL Pivot az egyik olyan technika, amely lehetővé teszi a sorok oszlopokba történő átültetését, valamint az út mentén lehetséges aggregációkat hajt végre. Az SQL PIVOT és az SQL UNPIVOT relációs operátorok egy táblázat értékű kétdimenziós adatokat egy másik adatformába ültetnek át. Az SQL PIVOT egy egyedi értékkészletből egy oszlopból több oszlopba ültet át egy táblázat értékű kifejezést a kimeneten, és aggregációkat hajt végre. Az SQL UNPIVOT az SQL PIVOT ellentétes működését hajtja végre úgy, hogy a táblázatos értékű kifejezés oszlopértékekké alakul át.
a legtöbb esetben a statikus elfordulási technika elegendő az üzleti követelményhez. Például a havi értékesítési előrejelzés, az éves értékesítési felosztás, a negyedéves értékesítési összesítés stb., ahol az in záradék oszlopai statikusak maradnak. Más esetekben szemcsés részletekre van szükségünk, a táblázat-Érték kifejezés dinamikusabb a természetben, és minden alkalommal, amikor a kifejezés új halmaza szerepel a táblázatban, akkor a dinamikus PIVOT lenne a legjobb választás. 
Megjegyzés: a fenti ábrából láthatjuk, hogy a folyamat PIVOT, az oszlop értékek forgatható a függőleges, hogy a vízszintes, mind a UNPIVOTING, mint a forgó, a vízszintes függőleges.
Syntax
SELECT <non-pivoted column>,
AS <column alias>,
AS <column alias>,
…
AS <column alias>
FROM
(
<SELECT QUERY>)
AS <Alias for temporary data set>
PIVOT
(
<Aggregate function>( Aggregate column)
FOR
IN ( , ,
… )
) AS <Pivot table alias>
<>;
első lépések az SQL pivot
az adatok elforgatásával és elforgatásával kezdjük. Most már értem, mi PIVOT és UNPIVOT operátorok SQL Server. Egyszerűen fogalmazva, ez csak egy másik módja annak, hogy leírjuk a különálló sorértékek saját oszlopokká alakításának folyamatát. Az oszlopok sorokká alakításának folyamata pedig nem elfordítható.
1. példa: SQL Pivot művelet végrehajtása
képzeljük el a AdventureWorks2014 adatbázisból származó minta adatkészletet. Ebben az esetben csak egy egyszerű adathalmazt kapott a SalesYear oszlopmal az egy mennyiségi Értékösszegek mentén.
most az adatkészlet készen áll a forgatásra. Az első oszlopban a SalesYear szerepel, a második oszlopban a teljes összeg. Ha az adatok átültetését kérte, akkor minden egyes évet az oszlopban kell megtennie, ezek az oszlopok pedig a pivot oszlopok fejlécévé válnak. Tehát 2011 lesz az első oszlop, 2012 lesz a saját oszlopa stb. A következő oszlop tényleges értékei összegezhetők, ragaszkodnak a pivot oszlop fejléceihez. Most, a forgóasztal úgy néz ki, mint az alábbiakban.
|
1
2
3
4
5
6
7
8
9
10
|
SELECT * FROM
(
VÁLASSZA ÉV(CHOI.OrderDate) mint SalesYear,
SOH.Részösszeg mint TotalSales
az értékesítésből.SalesOrderHeader SOH
csatlakozzon az értékesítéshez.SalesOrderDetail SOD a SOH.SalesOrderId = SOD.SalesOrderId
) mint értékesítés
PIVOT (SUM(TotalSales)
a SalesYear IN (,,,,)))
mint PVT
|
a következő kép a bemeneti adatokat ábrázolja, pivot szintaxis, SQL pivot és kimeneti eredmények.
2. példa: Hogyan hajtsunk végre egy SQL Unpivot műveletet
nézzük meg a másik irányba, nem elfordítva, ami olyan egyszerű,mint a vízszintesről a függőlegesre. Ebben az esetben, akkor vegye az összes különálló oszlopok vannak kiválasztva, és ez viszont azokat oszlop fejlécek saját sorokban. A forgás olyan, mint a függőleges vagy vízszintes forgatás. Az elfordulás pedig olyan, mintha a vízszintesről a függőlegesre forogna.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
VÁLASSZA ki SalesYear,
TotalSales
a
(
VÁLASSZA ki *
a
(
VÁLASSZA ÉV(CHOI.OrderDate) mint SalesYear,
SOH.Részösszeg mint TotalSales
az értékesítésből.SalesOrderHeader SOH
csatlakozzon az értékesítéshez.SalesOrderDetail SOD a SOH.SalesOrderId = SOD.SalesOrderId
), MINT Értékesítési PIVOT(SUM(TotalSales) A SalesYear A(,
,
,
)), MINT PVT
) T UNPIVOT(TotalSales A SalesYear A(,
,
,
)), MINT upvt;
|
a következő példában a elforgatható adatkészletben használt ensz-fordítsa felfelé az értékeket. A kulcselem itt az, hogy ezt könnyű volt átalakítani, mert közbenső lépésként létrehoztunk egy pivot táblát, majd elvégeztük az átalakítást a unpivot művelettel.
Megjegyzés: Az SQL pivot művelet az összesített eredmény oszlopba történő átültetését eredményezi, de mivel a pontos unpivot megfordítása nem SQL az oszlopok átültetése a sorok szegregált értékeibe. Az unpivot operátor nem osztja az összesített eredményeket.
3. példa: Hogyan lehet negyedéves értékesítést elérni az SQL pivot operation
használatával, nézzünk még néhány példát, hogy jobban megértsük a forgó fogalmakat. Ebben az esetben meglátjuk, hogyan lehet negyedéves értékesítési adatokat összesíteni a negyedéves eladások alapján.
mielőtt belevágnánk a megoldásba, mindig ajánlott egy adatkészlet létrehozása a PIVOT művelethez.
a következő példa adja meg az összesített havi eladások megoszlását az értékesítési évben.
|
1
2
3
4
5
6
7
8
9
|
VÁLASSZA ÉV(CHOI.OrderDate) mint SalesYear,
hónap (SOH.OrderDate) mint SalesMonth,
SUM (SOH.Részösszeg) a teljes értékként
az értékesítésből.SalesOrderHeader SOH
csatlakozzon az értékesítéshez.SalesOrderDetail SOD a SOH.SalesOrderId = SOD.SalesOrderId
csoportonként (SOH.OrderDate),
hónap (SOH.OrderDate)
rendelés évről évre (SOH.OrderDate),
hónap (SOH.Rendelve);
|
A következő kimeneti azt mutatja, hogy a bázis adatokat,
Ha kész az alap adatok akkor lehet alkalmazni, a VEZÉNYLŐ operátor. A következő példában az IN záradék Elfogad egy nem numerikus karaktert, amelyet a DATEPART érték alapján generáltak. A DATEPART értéket ” Q ” – val kombináljuk, majd az értékeket a PIVOT operátor záradékába tápláljuk.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
VÁLASSZA ki SalesYear,
ISNULL(, 0), MINT Q1,
ISNULL(, 0), MINT Q2,
ISNULL(, 0), MINT a Q3,
ISNULL(, 0), MINT a Q4,
(ISNULL(, 0) + ISNULL(, 0) + ISNULL (, 0) + ISNULL(, 0))) SalesYTD
(
válassza az év(SOH.OrderDate) mint SalesYear,
CAST (‘Q’ + CAST(DATEPART (negyedév, SOH.OrderDate) AS VARCHAR (1)) AS VARCHAR(2)) Quarters,
SOH.Részösszeg mint TotalSales
az értékesítésből.SalesOrderHeader SOH
csatlakozzon az értékesítéshez.SalesOrderDetail SOD a SOH.SalesOrderId = SOD.SalesOrderId
) Adatpivotként(SUM (TotalSales) negyedévekben (,
,
,
,
)) pvt
;
|
A következő kimenet negyedéves felosztása az eladási adatokat,
4. Példa: Hogyan havi értékesítési segítségével SQL pivot művelet
vessünk egy pillantást egy másik példa, hogy lásd az értékesítési osztott adatok alapján minden hónapban. A következő példában az in záradék értékek a DATEPART funkció részeként keletkeznek. A DATEPART értékek tápláljuk záradékában PIVOT operátor.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
VÁLASSZA ki SalesYear,
Isnull(, 0), mint Jan,
Isnull(, 0), mint Feb,
Isnull(, 0), mint Mar
ISNULL(, 0), mint Thm,
isnull(, 0), mint Lehet,
isnull(, 0), mint Jun
isnull(, 0), mint Jul,
isnull(, 0), mint Aug,
isnull(, 0) – Sep -,
ISNULL(, 0), mint Oct,
isnull(, 0), mint a November,
isnull(, 0), mint a Fogyasztást,
(isnull(, 0) + isnull(, 0) + ISNULL(, 0) + isnull(, 0) + isnull(, 0) + isnull(, 0) + ISNULL(, 0) + ISNULL(, 0) + isnull(, 0) + isnull(, 0) +isnull (, 0) + isnull (, 0) + isnull (, 0) + ISNULL (, 0) + ISNULL (, 0) + ISNULL (, 0))) SalesYTD
a
(
válassza az év (SOH.OrderDate) mint SalesYear,
DATEPART (hónap, SOH.OrderDate) hónap,
SOH.Részösszeg mint TotalSales
az értékesítésből.SalesOrderHeader SOH
csatlakozzon az értékesítéshez.SalesOrderDetail SOD a SOH.SalesOrderId = SOD.SalesOrderId
), MINT az Adatok PIVOT(SUM(TotalSales) A Hónap(,
,
,
,
,
,
,
,
,
,
,
)), MINT pvt;
|
A következő kimenet havi felosztása az eladási adatokat,
5. Példa: Hogyan havi értékesítési segítségével dinamikus SQL pivot művelet
foglalkozzunk a havi osztott értékesítési adatok segítségével dinamikus elforduló. Eddig a statikus pivot műveletekről beszéltünk. Annak érdekében, hogy egy statikus pivot műveletet dinamikussá alakítsunk, el kell távolítanunk a kódolt értékeket az IN záradékból. Először szerezze be a különálló kifejezést az adatkészletből, majd készítsen egy karakterláncot az összes kifejezés összefűzésével. A következő példában a @columnname az összes kifejezés összefűzésére szolgál. A konkatenált kifejezést a záradék elforgatására táplálják. A többi Egy egyszerű átalakítása statikus SQL egy dinamikus SQL hívás @DML segítségével sp_executesql tárolt eljárás.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
FELTÜNTETVE @dml, MINT NVARCHAR(MAX)
BEJELENTETT @ColumnName AS NVARCHAR (MAX)
SELECT @ColumnName= ISNULL (@ColumnName+’,’,”)
+ Quotename (Months)
FROM (SELECT DISTINCT DATEPART(MONTH, SOH.OrderDate) hónap
az értékesítésből.SalesOrderHeader SOH
csatlakozzon az értékesítéshez.SalesOrderDetail SOD a SOH.SalesOrderId = SOD.SalesOrderId
csoportonként (SOH.OrderDate),
DATEPART (hónap, SOH.OrderDate)) hónapként
— készítse elő a PIVOT lekérdezést a dinamikus
SET @dml =
N ‘select SalesYear,’ +@ColumnName + ‘ FROM
(
válassza ki az évet (SOH.OrderDate) mint SalesYear,
DATEPART (hónap, SOH.OrderDate) hónap,
SUM(SOH.Részösszeg) a teljes értékként
az értékesítésből.SalesOrderHeader SOH
csatlakozzon az értékesítéshez.SalesOrderDetail SOD a SOH.SalesOrderId = SOD.SalesOrderId
csoportonként (SOH.OrderDate),
DATEPART (hónap, SOH.Rendelve)) T
PIVOT(SUM(TotalSales)
A Hónap (‘ + @ColumnName + ‘)) MINT PVTTable’
–Print @DynamicPivotQuery
–Végre a Dinamikus Pivot Lekérdezés
EXEC sp_executesql @dml
|
Egyelőre ennyi…
becsomagolás
eddig már bizonyított, az alapvető fogalmakat, valamint több példát SQL FORDÍTSA meg az SQL UNPIVOT.
a minta az SQL Server adventureworks2014 adatbázison alapul. Megpróbálhatja kipróbálni az összes mintát. Ha bármilyen kérdése van, kérjük, hogy megjegyzést alább…
- Szerző
- Utolsó Hozzászólás
szakterületem a & magas rendelkezésre állási megoldások és platformközi DB migráció megvalósítása. A jelenleg működő technológiák az SQL Server, a PowerShell, az Oracle és a MongoDB.
összes Megtekintése hozzászólások Prashanth Jayaram
- Egy gyors áttekintést adatbázis ellenőrzés az SQL – január 28, 2021
- Hogyan kell beállítani az Azure Adatok Szinkronizálása között SQL Azure adatbázis, valamint az intézményi SQL Server – január 20, 2021
- Hogyan kell elvégezni az SQL Azure adatbázis Behozatali/Kiviteli műveletek segítségével PowerShell – január 14, 2021