Introducere
In ultimul meu articol „de Conversie Separate prin Virgulă Valoare Rânduri și Vice-Versa în SQL Server”, am vorbit despre modul în care puteți converti separate prin virgulă (sau separat cu un alt caracter) valori într-o singură coloană în rânduri și vice-versa. În acest articol, demonstrez cum puteți converti valorile rândurilor în valori coloane (PIVOT) și valori coloane în valori rânduri (UNPIVOT) în SQL Server.,SQL Server are un operator relațional PIVOT pentru a transforma valorile unice ale unei coloane specificate din mai multe rânduri în mai multe valori coloană în ieșire (cross-tab), rotirea în mod eficient un tabel. De asemenea, permite efectuarea agregărilor, ori de câte ori este necesar, pentru valorile coloanelor care sunt așteptate în rezultatul final. Sintaxa de bază pentru un operator relațional PIVOT arată astfel:
cu scriptul de mai jos, să creăm un tabel și să încărcăm câteva date în el., După cum puteți vedea în imaginea de mai jos, are informații despre vânzări pentru unele țări de câțiva ani. De asemenea, dacă observați, pentru fiecare țară și pentru fiecare an există un rând separat.
acum, folosind operatorul PIVOT, vom converti valorile rândului în valori de coloană cu scriptul dat mai jos și rezultatele așa cum se arată în imaginea de mai jos.
deși am folosit funcția de agregare a sumelor, în acest caz nu există însumare, deoarece există un singur rând pentru fiecare combinație unică pentru țară și an., Vă rugăm să rețineți utilizarea parantezelor pentru valorile coloanei pivot; acestea sunt necesare.
Aduce Dinamism PIVOT
Dacă observați în scriptul de mai sus, ne-au furnizat valori (2005, 2006, 2007 și 2008) pentru pivot coloane ca aceste valori sunt disponibile în seturile de date originale. Dar dacă se așteaptă ca unele valori suplimentare să vină în viitor, de exemplu 2009 și 2010 etc. pentru coloana de pivotare?,
în acest caz, puteți utiliza în continuare valorile coloanei pivot, care sunt așteptate să vină (sau care încă nu sunt disponibile în setul de date original) în viitor, deși veți vedea nul pentru valorile sale. Scriptul de mai jos arată acest scenariu, iar imaginea de mai jos arată NULLs pentru anii 2009 și 2010, deoarece nu există date pentru acești ani.
abordarea discutată mai sus funcționează bine dacă știți deja toate valorile posibile pentru coloana pivot, dar ce se întâmplă dacă nu?,
În acest caz, puteți scrie o interogare dinamic pentru prima apuca toate valorile unice pentru pivot de coloană la runtime și apoi o scrie de interogare dinamică să-l execute cu pivot interogare după cum se arată mai jos:
Conversie Coloane Rânduri să – UNPIVOT
UNPIVOT este un alt operator relațional în SQL Server care îndeplinește aproape operația inversă de PIVOT, prin rotirea valorile din coloane în rânduri valori. Permiteți-mi să demonstrez acest lucru cu un exemplu; vă permite să creați un tabel cu date pivotate din interogarea anterioară cu scriptul de mai jos., Imaginea de mai jos prezintă datele tabelului nou creat cu date pivotate.
pentru a roti valorile coloanelor în valori de rând, vom folosi acum operatorul UNPIVOT așa cum se arată mai jos. Imaginea de mai jos arată rotit de date:
Daca te referi înapoi, am spus UNPIVOT este aproape invers de PIVOT operator; acest lucru înseamnă că ar putea sau ar putea să nu fie exact la fel. Motivul este că Pivot efectuează agregarea în timp ce rotește valorile rândului în valori de coloană și ar putea îmbina posibile valori de rând multiple în valoare de coloană unică în ieșire., De exemplu, luați în considerare pentru o anumită țară și an există două valori, să zicem 5000 și 6000. Acum, când îl pivotați, ieșirea va avea 11000 ca valoare a coloanei dacă aveți SUM ca funcție de agregare. Mai târziu, dacă doriți să-l unpivot înapoi, veți obține 11000 nu valorile bi-furcated (5000 și 6000 ca original). Acestea fiind spuse, putem spune că dacă valorile pivotate sunt valori agregate, nu va fi posibilă recuperarea datelor originale.,
concluzie
În acest articol, am demonstrat cum puteți converti valorile rândului în valori coloană (PIVOT) și valorile coloanei în valori rând (UNPIVOT) în SQL Server. De asemenea, am vorbit despre scrierea unei interogări dinamice pentru a scrie o interogare pivot dinamică în care valorile posibile ale coloanei pivot sunt determinate în timpul rulării.