Tämä artikkeli antaa täydellisen yleiskatsauksen PIVOT- ja UNPIVOT-operaattoreiden käytöstä SQL Serverissä. PIVOT- ja UNPIVOT-operaattorit ovat samanlaisia kuin relaatiooperaattorit, jotka sallivat muuntaa taulukkoarvoisen lausekkeen toiseksi taulukoksi . Molemmat operaattorit luovat moniulotteisia raportteja, jotka auttavat yhdistämään ja vertailemaan suuria määriä dataa nopeasti.
Voimme käyttää PIVOT-operaattori kun meidän on muutettava taulukkoarvoisia lausekkeita. Se jakaa ainutlaatuiset arvot yhdestä sarakkeesta useisiin sarakkeisiin lopputuloksessa. Se myös aggregaatteja lopputuloksessa vaadittavat jäljellä olevat sarakearvot. UNPIVOT-operaattori muuntaa tiedot taulukkoarvoisen lausekkeen sarakkeista sarakearvoiksi, mikä on PIVOT:n käänteisarvo.
Ymmärrämme sen alla olevan yksinkertaisen kaavion avulla:
arraylist lajitella
Tämän kuvan vasemmalla puolella näemme alkuperäinen tietojoukko , jossa on kolme saraketta: Vuosi, alue, ja Myynti . Seuraavaksi näemme oikealla puolella olevan PIVOT-taulukon, joka on rakennettu kiertämällä Alue (rivit) pohjoiseen ja etelään (sarakkeet) . Kun rivit on muutettu sarakkeiksi, voimme tehdä a Myynti-sarakkeen arvojen aggregaatti jokaiselle PIVOT-taulukon sarakkeiden ja rivien leikkauspisteelle.
Luodaan ensin taulukko nimeltä pivot_demo PIVOT- ja UNPIVOT-operaattoreiden esittelyyn. Seuraava lause luo uuden taulukon määritettyyn tietokantaan:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
Lisää seuraavaksi tietoja tähän taulukkoon seuraavasti:
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);
Voimme tarkistaa tiedot SELECT-käskyllä. Saamme alla olevan tulosteen:
PIVOT-operaattori
Tätä operaattoria käytetään taulukkoarvoisten lausekkeiden kiertämiseen. Se esiteltiin ensimmäisen kerran SQL Server 2005 -versiossa. Se muuntaa tiedot riveistä sarakkeiksi. Se jakaa yksittäiset arvot yhdestä sarakkeesta useisiin sarakkeisiin ja kokoaa sitten loput sarakearvot, jotka vaaditaan lopputulokseen.
Meidän on noudatettava seuraavia vaiheita PIVOT-taulukon luomiseksi:
- Valitse käännettävä perustietojoukko.
- Luo väliaikaisia tuloksia johdetun taulukon tai CTE:n (yleinen taulukkolauseke) avulla.
- Käytä PIVOT-operaattoria.
Syntaksi
Seuraava syntaksi havainnollistaa PIVOTin käyttöä SQL Serverissä:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Jos rikomme tämän skriptin, voimme nähdä, että siinä on kaksi erillistä osaa. Ensimmäinen osa valitsee tiedot päätaulukosta, ja toinen osa määrittää, kuinka PIVOT-taulukko rakennetaan. Toinen osa sisältää myös erikoisavainsanoja, kuten SUM, FOR ja IN. Katsotaanpa näiden avainsanojen merkitys PIVOT-operaattorissa.
SUMMA
Tämä operaattori on tottunut koota arvot yhteen määritetystä sarakkeesta käytettäväksi PIVOT-taulukossa. Meidän on käytettävä sitä PIVOT-operaattorin kanssa saadakseen arvoosioiden kootut sarakenäytöt.
Avainsanalle
Tätä avainsanaa käytetään PIVOT-taulukon lausekkeelle opastaa PIVOT-operaattoria mihin sarakkeeseen PIVOT-toimintoa tulee käyttää. Pohjimmiltaan se osoittaa sarakkeiden nimet, jotka muuttuvat riveistä sarakkeiksi.
IN Avainsana
Tämä avainsana listaa kaikki ainutlaatuiset arvot PIVOT-sarakkeesta näytetään PIVOT-taulukon sarakkeina.
Esimerkki
Ymmärtäkäämme se erilaisten esimerkkien avulla.
1. Seuraava lause valitsee ensin vuosi-, pohjoinen- ja eteläsarakkeen kääntymisen perustiedoiksi. Luo sitten väliaikainen tulos johdetun taulukon avulla ja käytä lopuksi PIVOT-operaattoria luomaan lopullinen tulos. Tämä tuloste tilataan myös nousevaan vuoteen.
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
Tämän lausunnon suorittaminen tuottaa alla olevan tulosteen. Täällä voimme nähdä laskettu pohjoisen ja etelän alueen myynnin summa, joka vastaa vuoden arvoja .
2. Tämä on toinen esimerkki, jossa laskemme kunkin vuoden myynnin summan, joka vastaa alueen arvoja:
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;
Tämän lausunnon toteuttaminen tulee tuottaa virheen koska emme voi määrittää numeerista arvoa suoraan sarakkeen nimeksi.
SQL Serverin avulla voimme kuitenkin välttää tämän ongelman käyttämällä sulkuja ennen jokaista kokonaislukuarvoa. Päivitetty lausunto näkyy seuraavassa koodinpätkässä:
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;
Tämä lausunto suoritettiin onnistuneesti ja näyttää kunkin vuoden lasketun myyntisumman, joka vastaa alueen arvoja:
3. Edellinen esimerkki PIVOT-taulukon hankkimisesta on hyödyllinen, kun olemme tietoisia kaikista mahdollisista PIVOT-sarakearvoista. Mutta oletetaan, että tulevana vuonna sarakkeiden lukumäärä kasvaa. Edellisen esimerkin perusteella meillä on PIVOT-sarakkeina vuodet 2010, 2011 ja 2012. Ei kuitenkaan ole takeita siitä, että nämä sarakkeet eivät muutu tulevaisuudessa. Mitä tapahtuu, jos meillä on tietoja vuodesta 2013 tai 2014 tai ehkä jopa enemmän? Tällaisissa tapauksissa meidän on käytettävä dynaaminen PIVOT-taulukko kyselyitä tämän ongelman korjaamiseksi.
Dynaaminen PIVOT-taulukkokysely kapseloi koko PIVOT-komentosarjan tallennettuun toimintosarjaan. Tämä menettely tarjoaa säädettäviä vaihtoehtoja, joiden avulla voimme muuttaa vaatimuksiamme muuttamalla muutamia parametroituja arvoja.
kuinka määrittää näytön koko
Seuraava SQL-koodi selittää dynaamisen PIVOT-taulukon toiminnan. Tässä komentosarjassa olemme ensin hakeneet kaikki erilliset arvot PIVOT-sarakkeesta ja kirjoittaneet sitten SQL-käskyn suoritettavaksi PIVOT-kyselyn kanssa ajon aikana. Katsotaanpa tulos tämän skriptin suorittamisen jälkeen:
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
Tässä skriptissä olemme luoneet kaksi parametroitua muuttujaa. Sen kuvaus on alla:
@PivotColumn : Tämä muuttuja ottaa sarakkeen nimen alkuperäisestä taulukosta, johon PIVOT-taulukko on luotu. Esimerkiksi , tässä Alue-sarakkeessa näkyvät kaikki sarakkeissa käytettävissä olevat alueet.
@PivotList : Tämä muuttuja ottaa sarakeluettelon, jonka haluamme näyttää tulossarakkeena PIVOT-taulukossa.
Dynaamisen tallennetun toimenpiteen suorittaminen
Kun dynaaminen tallennettu toimintosarja on luotu onnistuneesti, olemme valmiita suorittamaan sen. Seuraavaa käskyä käytetään kutsumaan dynaaminen tallennettu toimintosarja PIVOT-taulukon näyttämiseksi ajon aikana:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Tässä olemme nyt määrittäneet sarakkeen nimen ' Alue ' ensimmäisenä parametrina ja PIVOT-sarakeluettelo toisena parametrina. Skriptin suorittaminen näyttää seuraavan tulosteen:
Nyt voimme lisätä sarakkeita tulevaisuudessa ajon aikana näyttääksemme PIVOT-taulukon, mikä ei ole mahdollista kahdessa ensimmäisessä esimerkissä.
UNPIVOT-operaattori
Se on käänteinen menetelmä PIVOT-operaattorille SQL Serverissä. Se mans tämä operaattori suorittaa PIVOTin päinvastainen toiminta muuntamalla tiedot sarakkeista riveiksi. UNPIVOT-operaattori kääntää myös PIVOT-taulukon tavalliseksi taulukoksi. Se esiteltiin ensimmäisen kerran SQL Server 2005 -versiossa.
Syntaksi
Seuraava syntaksi havainnollistaa UNPIVOTia SQL Serverissä:
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Esimerkki
Ymmärrämme, kuinka PIVOT-operaatio UNPIVOT-toiminto voidaan poistaa esimerkkien avulla. Luomme ensin alkuperäinen pöytä ja PIVOT-pöytä ja sovelsi sitten UNPIVOT-operaattoria tähän taulukkoon.
Seuraava koodinpätkä ilmoittaa ensin väliaikaisen taulukkomuuttujan @Tab:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
Seuraavaksi lisäämme arvot tähän taulukkoon seuraavasti:
matriisin kertolasku c:ssä
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
Nyt voimme suorittaa UNPIVOT-toiminnon käyttämällä alla olevaa lausuntoa:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Koodinpätkän suorittaminen palauttaa seuraavan tulosteen:
Alla oleva koodinpätkä on toinen esimerkki, joka suorittaa ensin PIVOT-toiminnon ja sitten UNPIVOT-toiminnon samassa taulukossa yhdessä kyselyssä:
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
Koodinpätkän suorittaminen näyttää saman tulosteen:
HUOMAUTUS: UNPIVOT-prosessi on PIVOT-menettelyn käänteinen toiminta, mutta se ei ole tarkka käännös. Koska rivit on yhdistetty, kun PIVOT laskee aggregaatin ja yhdistänyt tuloksessa useita rivejä yhdeksi riviksi, UNPIVOT-toiminto ei voi tehdä taulukosta alkuperäisen kaltaista. Jos PIVOT-operaattori ei kuitenkaan yhdistä useita rivejä yhdeksi riviksi, UNPIVOT-operaattori voi saada alkuperäisen taulukon PIVOT-tulosta.
Johtopäätös
Tämä artikkeli antaa täydellisen yleiskatsauksen PIVOT- ja UNPIVOT-operaattoreista SQL Serverissä ja muuntaa taulukkolausekkeen toiseksi. Älä koskaan unohda, että UNPIVOT on PIVOTin käänteinen toiminta, mutta se ei ole täsmälleen käänteinen PIVOT-tulokseen.