SQLShack

în acest articol, vom parcurge operatorii SQL Pivot și SQL UNPIVOT și cum pot fi utile pentru transpunerea datelor SQL Server. De asemenea, vom discuta despre modurile statice și dinamice de a utiliza operatorii relaționali PIVOT și UNPIVOT care pot fi utilizați pentru a transforma valorile distincte agregate ca coloane în setul de rezultate, specificând toate valorile coloanelor din clauza PIVOT IN.

SQL Server pivot Introducere

există mai multe moduri de a transpune un set de date de la rânduri la coloane și coloane la rânduri. SQL Pivot este una dintre tehnicile care permite transpunerea rândurilor în coloane și efectuează posibile agregări pe parcurs. SQL PIVOT și SQL UNPIVOT operatorii relaționali transpun o valoare de tabel date bidimensionale într-o altă formă de date. SQL PIVOT transpune o expresie cu valoare de tabel dintr-un set unic de valori dintr-o coloană în mai multe coloane din ieșire și efectuează agregări. SQL UNPIVOT efectuează operația opusă a SQL PIVOT prin transformarea coloanelor unei expresii cu valoare de tabel în valori de coloană.

în majoritatea cazurilor, tehnica de pivotare statică este suficientă pentru cerința de afaceri. De exemplu, prognoza lunară a vânzărilor, împărțirea anuală a vânzărilor, agregarea trimestrială a vânzărilor etc., unde coloanele clauzei IN rămân statice. În alte cazuri, avem nevoie de detalii granulare, iar expresia valorii tabelului este mai dinamică în natură și tot timpul setul nou al expresiei este inclus în tabel, atunci pivotul dinamic ar fi cea mai bună alegere.

notă: în descrierea de mai sus, putem vedea că în procesul de pivotare, valorile coloanei sunt rotite de la verticală la orizontală și UNPIVOTAREA este ca rotirea de la orizontală la verticală.

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>

< Ordonează după clauză>;

Noțiuni de bază cu SQL pivot

vom începe cu pivotarea și ne-pivotarea datelor. Până acum înțelegeți ce operatori PIVOT și UNPIVOT sunt în SQL Server. În termeni simpli, este doar un alt mod de a descrie procesul de transformare a valorilor distincte ale rândurilor în propriile coloane este cunoscut sub numele de pivotare. Și procesul de transformare a coloanelor în rânduri este cunoscut sub numele de ne-pivotant.

Exemplul 1: Cum se efectuează o operație SQL Pivot

să ilustrăm setul de date eșantion derivat din Baza de date AdventureWorks2014. În acest caz, tocmai ați primit un set de date simplu cu coloana SalesYear de-a lungul valorii cantitative totalsales.

acum, setul de date este pregătit pentru pivotare. Ai SalesYear în prima coloană, TotalSales în a doua coloană. Dacă ați cerut să transpuneți datele, trebuie să luați fiecare an distinct în coloană și aceste coloane devin antetul coloanelor pivot. Deci, 2011 devine prima coloană, 2012 devine propria coloană și așa mai departe. Valorile reale din coloana următoare Totalizeazăvânzări, se lipesc de anteturile coloanei pivot. Acum, tabelul pivotant ar arăta ca mai jos.

următoarea imagine descrie datele de intrare, pivot sintaxă, SQL pivot, și rezultatele de ieșire.

Exemplul 2: Cum se efectuează o operație SQL UNPIVOT

să ne uităm acum în cealaltă direcție, ne-pivotantă, care este la fel de simplă ca trecerea de la orizontală la verticală. În acest caz, ați lua toate acele coloane distincte care sunt selectate și ar transforma acele anteturi de coloană în propriile rânduri. Pivotarea este ca rotirea de la verticală la orizontală. Și ne-pivotarea este ca rotirea de la orizontală la verticală.

în exemplul următor, setul de date pivotat este utilizat pentru a dez-pivota valorile. Piesa cheie aici este acest lucru a fost ușor de făcut în obținerea transformat pentru că am fost capabili de a crea un tabel pivot ca un pas intermediar și apoi face transformarea folosind operațiunea unpivot.

notă: operațiunea SQL pivot are ca rezultat transpunerea rezultatului agregat în coloană, dar în timp ce SQL unpivot nu este o inversare exactă de transpunere a coloanelor în valorile segregate ale rândurilor. Operatorul unpivot nu va împărți rezultatele agregate.

Exemplul 3: Cum să obțineți vânzări trimestriale folosind operațiunea SQL pivot

să vedem câteva exemple pentru a înțelege mai bine conceptele pivotante. În acest caz, vom vedea cum să obțineți date trimestriale de vânzări agregate pe baza vânzărilor trimestriale.

înainte de a intra în soluție, este întotdeauna recomandat să lucrăm la crearea unui set de date pentru operația PIVOT.

următorul exemplu prezintă repartizarea lunară agregată a vânzărilor pe parcursul anului de vânzare.

următoarea ieșire arată datele de bază

odată ce sunteți gata cu datele de bază, puteți aplica operatorul pivot. În exemplul următor, clauza in acceptă un caracter non-numeric care a fost generat pe baza valorii DATEPART. Valoarea DATEPART este concatenată cu ‘ Q ‘ și apoi valorile sunt alimentate în clauza operatorului PIVOT.

următoarea ieșire este o împărțire trimestrială a datelor de vânzări

Exemplul 4: Cum să obțineți vânzări lunare folosind o operațiune SQL pivot

să aruncăm o privire într-un alt exemplu pentru a vedea datele de vânzări split, bazate pe fiecare lună. În exemplul următor, valorile clauzei IN sunt generate ca parte a funcției DATEPART. Valorile DATEPART sunt alimentate în clauza operatorului PIVOT.

următoarea ieșire este o împărțire lunară a datelor de vânzări

exemplul 5: Cum să obțineți vânzări lunare folosind operațiunea dinamică SQL pivot

să ne abordați împărțirea lunară a datelor de vânzări utilizând pivotarea dinamică. Până acum, am discutat despre operațiile de pivotare statică. Pentru a converti o operație pivot statică în dinamică, trebuie să eliminăm valorile codificate din clauza IN. Mai întâi, obțineți expresia distinctă din setul de date și apoi pregătiți un șir prin concatenarea tuturor expresiilor. În exemplul următor, @ columnname este folosit pentru a concatena toată expresia. Expresia concatenată este alimentată pentru a pivota în clauză. Restul este o conversie simplă a SQL static într-un SQL dinamic și apel @DML folosind procedura stocată sp_executesql.

asta e tot pentru acum…

înfășurându-ne

până acum, am demonstrat conceptele de bază și câteva exemple de SQL pivot și SQL unpivot.

eșantionul se bazează pe baza de date SQL Server adventureworks2014. Puteți încerca și testa singur toate probele. Dacă aveți orice întrebare, vă rugăm să nu ezitați să comenteze mai jos…

• autor
• Posturi recente

> sunt un tehnolog de baze de date care are peste 11 ani de experiență bogată și practică în domeniul tehnologiilor de baze de date. Sunt Microsoft Certified Professional și susținut cu o diplomă în Master of Computer Application.
specialitatea mea constă în proiectarea& implementarea soluțiilor de înaltă disponibilitate și migrarea DB pe mai multe platforme. Tehnologiile care lucrează în prezent sunt SQL Server, PowerShell, Oracle și MongoDB.
Vizualizați toate postările lui Prashanth Jayaram

ultimele postări ale lui Prashanth Jayaram (vezi toate)

• o prezentare rapidă a auditului bazei de date în SQL – 28 Ianuarie 2021
• cum să configurați sincronizarea datelor Azure între bazele de date Azure SQL și sql server local-20 ianuarie 2021
• cum se efectuează operațiuni de import/export de baze de date Azure SQL folosind PowerShell – 14 ianuarie 2021