In dit artikel gaan we door de SQL Pivot en SQL Unpivot operators en hoe ze nuttig kunnen zijn om SQL Server data te transponeren. Ook zullen we zowel statische als dynamische manieren bespreken om PIVOT en UNPIVOT relationele operators te gebruiken die kunnen worden gebruikt om geaggregeerde afzonderlijke waarden als kolom(s) in de resultaatset te transformeren door alle kolomwaarden in de PIVOT IN-clausule op te geven.
SQL Server pivot Introduction
er zijn verschillende manieren om een dataset van rijen naar kolommen en kolommen naar rijen te transponeren. SQL Pivot is een van de technieken waarmee het transponeren van rijen naar kolommen en voert mogelijke aggregaties langs de weg. SQL PIVOT en SQL UNPIVOT relationele operatoren transponeren een tabel-waarde tweedimensionale gegevens in een andere vorm van gegevens. SQL PIVOT transponeert een tabel-waarde expressie van een unieke set van waarden uit een kolom in meerdere kolommen in de output en voert aggregaties. SQL UNPIVOT voert de tegenovergestelde bewerking van SQL-PIVOT uit door de kolommen van een tabelgewaardeerde expressie om te zetten in kolomwaarden.
in de meeste gevallen voldoet de statische draaitechniek aan de zakelijke eis. Bijvoorbeeld de maandelijkse verkoopprognose, de jaarlijkse verkoopopsplitsing, de driemaandelijkse verkoopaggregatie, enz., waarbij de in-clausule kolommen statisch blijven. In sommige andere gevallen hebben we korrelige details nodig en de tabelwaarde-expressie is dynamischer van aard en de hele tijd zijn nieuwe verzamelingen van de expressie opgenomen in de tabel dan is dynamische PIVOT de beste keuze.
opmerking: in de bovenstaande afbeelding kunnen we zien dat tijdens het draaiproces de kolomwaarden worden geroteerd van verticaal naar horizontaal en het uitpakken is als het draaien van horizontaal naar verticaal.
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>
< volgorde volgens clausule>;
aan de slag met SQL Pivot
We gaan beginnen met het draaien en het ongedaan maken van de gegevens. Inmiddels begrijpt u wat PIVOT en UNPIVOT operators zijn in SQL Server. In eenvoudige termen is het gewoon een andere manier om het proces van het draaien van verschillende rijwaarden in hun eigen kolommen te beschrijven staat bekend als Pivoting. En het proces van het omzetten van kolommen in rijen staat bekend als een un-pivoting.
Voorbeeld 1: Hoe een SQL-draaibewerking
uit te voeren laten we de voorbeelddataset van de AdventureWorks2014-database in beeld brengen. In dit geval heb je gewoon een eenvoudige dataset met SalesYear kolom langs de ene kwantitatieve waarde TotalSales.
nu is de dataset klaar om te draaien. Je hebt verkoopjaar in de eerste kolom, totale verkoop in de tweede kolom. Als u hebt gevraagd om de gegevens te transponeren, moet u elk afzonderlijk jaar in de kolom opnemen en deze kolommen worden de koptekst van de draaikolommen. Dus 2011 wordt de eerste kolom, 2012 wordt zijn eigen kolom enzovoort. De werkelijke waarden uit de volgende kolom totale verkoop, ze blijven bij de draaikolomkoppen. Nu zou de draaitabel er hieronder uitzien.
|
1
2
3
4
5
6
7
8
9
10
|
SELECT * FROM
(
SELECTEER het JAAR(SOH.Orderdatum) als verkoopjaar,
SOH.Subtotaal als totale verkopen
van verkopen.SalesOrderHeader SOH
JOIN sales.Verkooporderdetail SOD op SOH.SalesOrderId = SOD.SalesOrderId
) AS Sales
PIVOT (SUM(TotalSales)
voor SalesYear in (,,,))
als PVT
|
de volgende afbeelding toont de invoergegevens, pivot syntaxis, sql pivot, en output resultaten.
Voorbeeld 2: Hoe een SQL Unpivot operatie uit te voeren
laten we nu kijken in de andere richting, un-pivoting, dat is zo eenvoudig als het gaan van horizontaal naar verticaal. In dit geval neemt u alle afzonderlijke kolommen die zijn geselecteerd en verandert deze kolomkoppen in hun eigen rijen. Draaien is als draaien van verticaal naar horizontaal. En un-pivoting is als roteren van horizontaal naar verticaal.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SELECTEER SalesYear,
TotalSales
VAN de
(
SELECT *
VAN de
(
SELECTEER het JAAR(SOH.Orderdatum) als verkoopjaar,
SOH.Subtotaal als totale verkopen
van verkopen.SalesOrderHeader SOH
JOIN sales.Verkooporderdetail SOD op SOH.SalesOrderId = SOD.SalesOrderId
) ALS Sales PIVOT(SUM(TotalSales) VOOR SalesYear IN(,
,
,
)) ALS PVT
) T UNPIVOT(TotalSales VOOR SalesYear IN(,
,
,
)) ALS upvt;
|
In het volgende voorbeeld, de gelagerde de dataset is gebruikt om de vn-draai de waarden. Het belangrijkste stuk hier is dat dit was gemakkelijk te doen in getting transformed omdat we in staat waren om een draaitabel te maken als een tussenstap en dan doen de transformatie met behulp van de unpivot operatie.
opmerking: SQL-pivot-bewerking resulteert in het transponeren van het geaggregeerde resultaat in kolom, maar terwijl SQL-unpivot geen exacte omkering is van het transponeren van kolommen naar de kolom gescheiden waarden van rijen. De unpivot operator zal de geaggregeerde resultaten niet splitsen.
Voorbeeld 3: Hoe kwartaalomzet te krijgen met behulp van SQL pivot operatie
laten we nog een paar voorbeelden zien om de pivoting Concepten beter te begrijpen. In dit geval zullen we zien hoe u kwartaalgegevens kunt verzamelen op basis van de kwartaalverkoop.
voordat we in de oplossing springen, wordt het altijd aanbevolen om te werken aan het maken van een dataset voor de draaibewerking.
het volgende voorbeeld geeft het geaggregeerde maandelijkse verkoopspread over het verkoopjaar.
|
1
2
3
4
5
6
7
8
9
|
SELECTEER het JAAR(SOH.Orderdatum) als verkoopjaar,
maand (SOH.Orderdatum) als verkoopmaand,
Som (SOH.Subtotaal) als totale verkopen
van verkopen.SalesOrderHeader SOH
JOIN sales.Verkooporderdetail SOD op SOH.SalesOrderId = SOD.Verkooporderid
groep per jaar (SOH.Orderdatum),
maand (SOH.Orderdatum)
volgorde per jaar (SOH.Orderdatum),
maand (SOH.Volgorde);
|
De volgende uitvoer toont de basisgegevens
als u klaar bent met de basisgegevens, kunt u de pivot-Operator toepassen. In het volgende voorbeeld accepteert de in-clausule een niet-numeriek karakter dat werd gegenereerd op basis van de datepart-waarde. De DATEPART-waarde wordt samengevoegd met’ Q ‘ en vervolgens worden de waarden ingevoerd IN de clausule van PIVOT operator.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SELECTEER SalesYear,
ISNULL(, 0) ALS Q1,
ISNULL(, 0) ALS K2
ISNULL(, 0) ALS K3
ISNULL(, 0) ALS K4,
(ISLEEG(, 0) + ISNULL(, 0) + ISNULL(, 0) + ISNULL(, 0)) SalesYTD
FROM
(
SELECT YEAR(SOH.Orderdatum) als verkoopjaar,
CAST (“Q” +CAST (DATEPART (QUARTER, SOH.Orderdatum) als VARCHAR (1)) als VARCHAR(2)) kwartalen,
SOH.Subtotaal als totale verkopen
van verkopen.SalesOrderHeader SOH
JOIN sales.Verkooporderdetail SOD op SOH.SalesOrderId = SOD.SalesOrderId
) AS Data PIVOT (SUM(totale verkopen) voor kwartalen in(,
,
,
)) AS pvt
volgorde per verkoopjaar;
|
De volgende output is een driemaandelijkse uitsplitsing van de omzet gegevens
Voorbeeld 4: Hoe krijgt maandelijks verkoop met behulp van een SQL-pivot operatie
Laten we een kijkje nemen in een ander voorbeeld te zien van verdeling van omzet gegevens op basis van elke maand. In het volgende voorbeeld worden de in-clausule waarden gegenereerd als onderdeel van de datepart functie. De datepart waarden worden ingevoerd IN clausule van PIVOT operator.
|
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
|
SELECTEER SalesYear,
Isnull(, 0) als Jan,
Isnull(, 0) als Jan,
Isnull(, 0) als Mar,
ISNULL(, 0) als Apr,
isnull(, 0) als Mogelijk,
isnull(, 0) als Jun,
isnull(, 0) als Jul,
isnull(, 0) als Aug,
isnull(, 0) als Sep,
ISNULL(, 0) de Oct
isnull(, 0) als Nov,
isnull(, 0) zo Dec,
(isleeg(, 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
uit de
(
SELECT YEAR (SOH.Orderdatum) als verkoopjaar,
DATEPART (MONTH, SOH.Orderdatum) maanden,
SOH.Subtotaal als totale verkopen
van verkopen.SalesOrderHeader SOH
JOIN sales.Verkooporderdetail SOD op SOH.SalesOrderId = SOD.SalesOrderId
) ALS Gegevens PIVOT(SUM(TotalSales) VOOR Maanden IN(,
,
,
,
,
,
,
,
,
,
,
)) ALS pvt;
|
de volgende output is een maandelijkse splitsing van de verkoopgegevens
Voorbeeld 5: Hoe krijg je maandelijkse verkopen met behulp van dynamische SQL pivot operatie
laten we de maandelijkse splitsing van verkoopgegevens met behulp van dynamische pivoting. Tot nu toe hebben we statische draaibewegingen besproken. Om een statische draaibewerking naar dynamisch te converteren, moeten we de hardcoded-waarden uit de in-clausule verwijderen. Haal eerst de verschillende expressie uit de dataset en bereid vervolgens een string voor door alle expressies samen te voegen. In het volgende voorbeeld wordt de @columnnaam gebruikt om alle expressie samen te voegen. De aaneengeschakelde uitdrukking wordt gevoed om in de zin te draaien. De rest is een eenvoudige conversie van statische SQL in een dynamische SQL en call @DML met behulp van sp_executesql opgeslagen procedure.
|
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
|
onder VERMELDING van @dml ALS NVARCHAR(MAX)
VERKLAARD @ColumnName als nvarchar (MAX)
SELECT @ColumnName= ISNULL (@ColumnName + ‘,’,”)
+ QUOTENAME (Months)
FROM(selecteer afzonderlijke DATEPART (MONTH, SOH.Orderdatum) maanden
van de verkoop.SalesOrderHeader SOH
JOIN sales.Verkooporderdetail SOD op SOH.SalesOrderId = SOD.Verkooporderid
groep per jaar (SOH.Orderdatum),
DATEPART (MONTH, SOH.OrderDate)) AS Months
–Prepare the PIVOT query using the dynamic
SET @dml =
N ‘select SalesYear,’ +@ColumnName + ‘ FROM
(
SELECT YEAR (SOH.Orderdatum) als verkoopjaar,
DATEPART (MONTH, SOH.Orderdatum) maanden,
Som (SOH.Subtotaal) als totale verkopen
van verkopen.SalesOrderHeader SOH
JOIN sales.Verkooporderdetail SOD op SOH.SalesOrderId = SOD.Verkooporderid
groep per jaar (SOH.Orderdatum),
DATEPART (MONTH, SOH.OrderDate)) ALS T
PIVOT(SUM(TotalSales)
VOOR Maanden IN (‘ + @ColumnName + ‘)) ALS PVTTable’
–Afdrukken @DynamicPivotQuery
–Uitvoeren van de Dynamische Spil Query
EXEC sp_executesql @dml –
|
Dat is alles voor nu…
inpakken
tot nu toe, we hebben aangetoond worden de basisbegrippen en de verschillende voorbeelden van SQL-PIVOT en SQL UNPIVOT.
het voorbeeld is gebaseerd op de SQL Server adventureworks2014 database. U kunt proberen en testen van alle monsters zelf. Als u een vraag hebt, kunt u hieronder commentaar geven…
- auteur
- recente berichten
- Een overzicht van de audit database in SQL – 28 januari 2021
- het instellen van Azure Data Sync tussen Azure SQL-databases en lokale SQL Server – 20 januari 2021
- het uitvoeren van SQL Azure database Import/Export activiteiten met behulp van PowerShell – 14 januari 2021
mijn specialiteit ligt in het ontwerpen van & implementeren van High availability oplossingen en cross-platform DB migratie. De technologieën die momenteel werken aan zijn SQL Server, PowerShell, Oracle en MongoDB.
Bekijk alle berichten van Prashanth Jayaram