Acest articol va oferi o prezentare completă a utilizării operatorilor PIVOT și UNPIVOT în SQL Server. Operatorii PIVOT și UNPIVOT sunt similari cu operatorii relaționali care permit acest lucru transformând expresia cu valori de tabel într-un alt tabel . Ambii operatori generează rapoarte multidimensionale care ajută la combinarea și compararea rapidă a unei cantități mari de date.
Putem folosi operator PIVOT când trebuie să transformăm expresii cu valori de tabel. Se împarte valori unice dintr-o coloană în mai multe coloane în rezultatul final. De asemenea agregate valorile rămase ale coloanei necesare în rezultatul final. operator UNPIVOT convertește datele din coloanele unei expresii cu valori de tabel în valori de coloană, care este inversul PIVOT.
Să-l înțelegem cu ajutorul diagramei simple prezentate mai jos:
În partea stângă a acestei figuri, putem vedea setul de date original , care are trei coloane: an, regiune, și Vânzări . În continuare, putem vedea tabelul PIVOT din partea dreaptă, care este construit prin rotirea Regiune (rânduri) în nord și sud (coloane) . După convertirea rândurilor în coloane, putem face o agregat al valorilor coloanei Vânzări pentru fiecare intersecție dintre coloanele și rândurile tabelului PIVOT.
Mai întâi să creăm un tabel numit pivot_demo pentru a demonstra operatorii PIVOT și UNPIVOT. Următoarea instrucțiune creează un nou tabel în baza de date specificată:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
Apoi, introduceți câteva date în acest tabel, după cum urmează:
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);
Putem verifica datele folosind instrucțiunea SELECT. Vom obține rezultatul de mai jos:
Operator PIVOT
Acest operator este folosit pentru a roti expresiile cu valori de tabel. A fost introdus pentru prima dată în versiunea SQL Server 2005. Convertește datele din rânduri în coloane. Împarte valorile unice dintr-o coloană în mai multe coloane și apoi adună valorile rămase ale coloanei necesare în rezultatul final.
Trebuie să urmăm următorii pași pentru a crea un tabel PIVOT:
- Selectați setul de date de bază pentru pivotare.
- Creați rezultate temporare cu ajutorul unui tabel derivat sau CTE (expresie de tabel comună).
- Utilizați operatorul PIVOT.
Sintaxă
Următoarea sintaxă ilustrează utilizarea PIVOT în SQL Server:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Dacă întrerupem acest script, putem vedea că are două secțiuni separate. Prima secțiune selectează datele din tabelul principal, iar a doua secțiune determină modul în care va fi construit tabelul PIVOT. A doua parte conține, de asemenea, câteva cuvinte cheie speciale, cum ar fi SUM, FOR și IN. Să vedem semnificația acestor cuvinte cheie în operatorul PIVOT.
100 km/h în mph
SUMĂ
Acest operator este obișnuit cumulează valorile din coloana specificată pentru a fi utilizată în tabelul PIVOT. Trebuie să-l folosim cu operatorul PIVOT pentru a obține afișajele agregate ale coloanei pentru secțiunile de valori.
PENTRU cuvânt cheie
Acest cuvânt cheie este folosit pentru instrucțiunea tabelului PIVOT to instruiți operatorul PIVOT pe care coloană trebuie aplicată funcția PIVOT. Practic, indică numele coloanelor care se vor transforma din rânduri în coloane.
IN Cuvânt cheie
Acest cuvânt cheie listează toate valorile unice din coloana PIVOT pentru a fi afișate ca coloane ale tabelului PIVOT.
exemplu java salut lume
Exemplu
Să-l înțelegem cu ajutorul diverselor exemple.
1. Următoarea instrucțiune selectează mai întâi coloana An, Nord și Sud ca date de bază pentru pivotare. Apoi, creați un rezultat temporar folosind tabelul derivat și, în final, aplicați operatorul PIVOT pentru a genera rezultatul final. Această ieșire este, de asemenea, ordonată în Anul crescător.
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
Executarea acestei instrucțiuni va produce rezultatul de mai jos. Aici, putem vedea suma calculată a vânzărilor din regiunile de nord și de sud corespunzătoare valorilor anului .
2. Acesta este un alt exemplu în care vom calcula suma vânzărilor pentru fiecare An corespunzătoare valorilor regiunii:
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;
Executarea acestei declarații va produce o eroare deoarece nu putem specifica valoarea numerică ca nume de coloană direct.
Cu toate acestea, SQL Server ne permite să evităm această problemă utilizând parantezele dinaintea fiecărei valori întregi. Declarația actualizată este afișată în următorul fragment de cod:
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;
Această declarație a fost executată cu succes și afișează suma calculată a vânzărilor pentru fiecare an corespunzătoare valorilor regiunii:
3. Exemplul anterior de obținere a unui tabel PIVOT este util atunci când cunoaștem toate valorile posibile ale coloanei PIVOT. Dar să presupunem că în anul următor numărul de coloane crește. Luând în considerare exemplul precedent, avem anii 2010, 2011 și 2012 ca coloane PIVOT. Cu toate acestea, nu există nicio garanție că aceste coloane nu se vor modifica în viitor. Ce se întâmplă dacă avem date din 2013 sau 2014, sau poate chiar mai mult? În astfel de cazuri, va trebui să folosim tabel PIVOT dinamic interogări pentru a rezolva această problemă.
Interogarea tabelului PIVOT dinamic încapsulează întregul script PIVOT într-o procedură stocată. Această procedură va oferi opțiuni ajustabile, permițându-ne să ne modificăm cerințele prin modificarea câtorva valori parametrizate.
conversia int în java dublu
Următorul cod SQL explică funcționarea tabelului PIVOT dinamic. În acest script, am preluat mai întâi toate valorile distincte din coloana PIVOT și apoi am scris o instrucțiune SQL pentru a fi executată cu interogarea PIVOT în timpul execuției. Să vedem rezultatul după executarea acestui script:
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
În acest script, am creat două variabile parametrizate. Descrierea acestuia este dată mai jos:
@PivotColumn : Această variabilă va prelua numele coloanei din tabelul original pe care este creat tabelul PIVOT. De exemplu , aici, coloana „Regiune” afișează toate regiunile disponibile în coloane.
@PivotList : Această variabilă va prelua lista de coloane pe care dorim să o afișam ca coloană de ieșire în tabelul PIVOT.
Executarea procedurii stocate dinamice
După crearea cu succes a procedurii dinamice stocate, suntem gata să o executăm. Următoarea instrucțiune este utilizată pentru a apela procedura stocată dinamică pentru a afișa tabelul PIVOT în timpul execuției:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Aici am specificat acum numele coloanei ' Regiune ' ca prim parametru și lista de coloane PIVOT ca al doilea parametru. Executarea scriptului va afișa următoarea ieșire:
Acum, putem adăuga mai multe coloane în viitor la runtime pentru a afișa tabelul PIVOT, ceea ce nu este posibil cu primele două exemple.
Operator UNPIVOT
Este metoda inversă a operatorului PIVOT din SQL Server. Acesta este operatorul care efectuează funcţionarea opusă a PIVOT-ului prin conversia datelor din coloane în rânduri. Operatorul UNPIVOT rotește și tabelul PIVOT în tabelul obișnuit. A fost introdus pentru prima dată în versiunea SQL Server 2005.
Sintaxă
Următoarea sintaxă ilustrează UNPIVOT în SQL Server:
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Exemplu
dimensiunea pitonului
Să înțelegem cum să UNPIVOT operația PIVOT cu exemple. Mai întâi vom crea un masa originala si masa PIVOT și apoi aplicat operator UNPIVOT pe acest tabel.
Următorul fragment de cod declară mai întâi o variabilă temporară de tabel @Tab:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
În continuare, vom introduce valori în acest tabel după cum urmează:
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
Acum, putem efectua operația UNPIVOT utilizând instrucțiunea de mai jos:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Executarea fragmentului de cod va returna următoarea ieșire:
Fragmentul de cod de mai jos este un alt exemplu pentru a efectua mai întâi operația PIVOT și apoi operația UNPIVOT pe același tabel într-o singură interogare:
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
Executarea fragmentului de cod va afișa aceeași ieșire:
NOTĂ: Procesul UNPIVOT este o operație inversă a procedurii PIVOT, dar nu este o inversare exactă. Deoarece rândurile au fost îmbinate atunci când PIVOT calculează agregatul și combină multe rânduri într-un singur rând în rezultat, prin urmare, operația UNPIVOT nu poate face ca tabelul ca originalul. Cu toate acestea, dacă operatorul PIVOT nu îmbină multe rânduri într-un singur rând, atunci operatorul UNPIVOT poate obține tabelul original din ieșirea PIVOT.
Concluzie
Acest articol va oferi o prezentare completă a operatorilor PIVOT și UNPIVOT din SQL Server și va converti o expresie de tabel în alta. Nu trebuie să uităm niciodată că UNPIVOT este operația inversă a PIVOT, dar nu este exact inversul rezultatului PIVOT.