tässä artikkelissa käydään läpi SQL Pivot ja SQL Unpivot operaattorit ja miten ne voivat olla hyödyllisiä transponoida SQL Server data. Lisäksi keskustelemme sekä staattisista että dynaamisista tavoista käyttää PIVOT-ja UNPIVOT-relaatiooperaattoreita, joita voidaan käyttää muuttamaan yhteenlaskettuja erillisiä arvoja sarakkeina tulosjoukossa määrittämällä kaikki pivot-sarakkeen arvot lausekkeessa.
SQL Server pivot Introduction
on olemassa useita tapoja siirtää tietojoukko riveistä sarakkeisiin ja sarakkeista riveihin. SQL Pivot on yksi tekniikoista, joka mahdollistaa rivien siirtämisen sarakkeisiin ja suorittaa mahdollisia aggregaatioita matkan varrella. SQL PIVOT ja SQL UNPIVOT relaatio-operaattorit transponoivat taulukon arvoisen kaksiulotteisen datan toiseen datamuotoon. SQL PIVOT Transponoi taulukon arvoisen lausekkeen uniikista arvojoukosta yhdestä sarakkeesta useisiin sarakkeisiin tuotoksessa ja suorittaa aggregaatioita. SQL UNPIVOT suorittaa SQL PIVOT: n vastakkaisen operaation muuntamalla taulukon arvoisen lausekkeen sarakkeet sarakearvoiksi.
useimmissa tapauksissa staattinen kääntymistekniikka riittää liiketoiminnan vaatimukseen. Esimerkiksi kuukausittainen myyntiennuste, vuosimyynnin jako, neljännesvuosimyynnin aggregointi jne., jossa lausekkeiden sarakkeet pysyvät muuttumattomina. Joissakin muissa tapauksissa tarvitsemme rakeisia yksityiskohtia ja taulukko-arvo ilmaisu on dynaamisempi luonteeltaan ja koko ajan uusia joukko lausekkeen sisältyvät taulukkoon sitten dynaaminen PIVOT olisi paras valinta.
huomaa: edellä olevasta kuvauksesta voidaan nähdä, että sarakkeen arvot kiertyvät pystystä vaakatasoon ja KÄÄNTYMÄTTÖMYYS on kuin pyörittäisi sitä vaakatasosta pystyyn.
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>
< ORDER BY clause>;
SQL pivot
aloitetaan kääntämällä ja irrottamalla data. Nyt ymmärrät, mitä PIVOT ja UNPIVOT operaattorit ovat SQL Server. Yksinkertaisesti sanottuna, se on vain yksi tapa kuvata prosessia kääntämällä erilliset riviarvot omiin sarakkeisiin tunnetaan Pivoting. Ja prosessi kääntämällä sarakkeet riveiksi tunnetaan kääntymättömänä.
Esimerkki 1: Kuinka suorittaa SQL Pivot-operaatio
kuvitellaan adventureworks2014-tietokannasta johdettu otosaineisto. Tässä tapauksessa, olet juuri saanut yksinkertainen tietokokonaisuus SalesYear sarake pitkin yksi kvantitatiivinen arvo yhteensä.
nyt aineisto on kääntövalmis. SalesYear on ensimmäisessä sarakkeessa, TotalSales toisessa sarakkeessa. Jos olet pyytänyt siirtää tiedot, sinun täytyy ottaa jokainen erillinen vuosi sarakkeessa ja nämä sarakkeet tullut otsikko pivot sarakkeet. Niin, 2011 tulee ensimmäinen sarake, 2012 tulee oma sarake ja niin edelleen. Todelliset arvot seuraavasta sarakkeesta yhteensä, ne kiinni pivot sarakkeen otsikot. Kääntyvä taulukko näyttäisi olevan alla.
|
1
2
3
4
5
6
7
8
9
10
|
seuraava kuva kuvaa syöttötietoja pivot syntaksi, SQL pivot, ja tulostuksen tulokset.
Esimerkki 2: Miten suorittaa SQL Unpivot operaatio
Katsotaanpa nyt toiseen suuntaan, un-kääntyvä, joka on niinkin yksinkertaista kuin menee vaakasuorasta pystysuoraan. Tässä tapauksessa, ottaisit kaikki ne erilliset sarakkeet, jotka on valittu, ja se muuttaisi sarakkeiden otsikot omiksi riveikseen. Kääntyminen on kuin pyörisi pystysuorasta vaakatasoon. Kääntymättömyys on kuin pyörisi vaakatasosta pystyyn.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
9 |
select salesyear,
totalsales
from the
(
valitse *
alkaen
(
valitse vuosi(soh.OrderDate) nimellä SalesYear,
SOH.Välisumma Kokonaissaleina
myynnistä.SalesOrderHeader SOH
JOIN sales.Myyntitilauksetyksityiskohtainen SOD ON SOH.SalesOrderId = SOD.SalesOrderId
) AS Sales PIVOT(SUM(TotalSales) FOR SalesYear IN(,
,
) AS PVT
) T UNPIVOT(TotalSales FOR SalesYear IN (,
,
,
)) AS upvt;
|
seuraavassa esimerkissä arvojen kääntämiseen käytetään nivellettyä tietojoukkoa. Avain pala tässä on tämä oli helppo tehdä saada muuttunut, koska pystyimme luomaan pivot taulukko välivaiheena ja sitten tehdä muutos käyttäen unpivot toimintaa.
Huom: SQL pivot-operaatio johtaa yhdistetyn tuloksen siirtämiseen sarakkeeseen, mutta SQL unpivot ei ole eksakti transposoivien sarakkeiden kääntäminen rivien erotelluiksi arvoiksi. Unpivot-operaattori ei jaa koottuja tuloksia.
esimerkki 3: Miten saada neljännesvuosimyynti SQL pivot operation
Katsotaanpa muutamia esimerkkejä ymmärtää kääntyvä käsitteitä paremmin. Tässä tapauksessa, näemme, miten saada neljännesvuosittainen myyntitiedot aggregoidaan perustuu neljännesvuosimyynti.
ennen kuin hyppäämme ratkaisuun, on aina suositeltavaa työstää tietokokonaisuutta PIVOT-operaatiota varten.
seuraavassa esimerkissä on laskettu yhteen kuukausimyynnin jakauma myyntivuodelle.
|
1
2
3
4
5
6
7
8
9
|
valitse vuosi(soh.Tilauspäivä) Myyntivuonna,
kuukausi(SOH.OrderDate) SalesMonth,
SUM(SOH.Välisumma) summina
myynnistä.SalesOrderHeader SOH
JOIN sales.Myyntitilauksetyksityiskohtainen SOD ON SOH.SalesOrderId = SOD.SalesOrderId
GROUP BY YEAR(SOH.Päiväys),
kuukausi(SOH.Järjestyksenpito)
järjestys vuoden mukaan(SOH.Päiväys),
kuukausi(SOH.OrderDate);
|
seuraavan tuotoksen pohjatiedot
kun perusdata on valmis, voit käyttää pivot-operaattoria. Seuraavassa esimerkissä lauseke hyväksyy ei-numeerisen merkin, joka syntyi DATEPART-arvon perusteella. DATEPART-arvo on concatenated kanssa ” Q ” ja sitten arvot syötetään lauseke PIVOT operaattori.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
8 |
valitse salesyear,
isnull(, 0) Q1: nä,
isnull(, 0) Q2: na,
isnull(, 0) as Q3,
isnull(, 0) as Q4,
(isnull(, 0) + isnull(, 0) + ISNULL(, 0) + ISNULL(, 0)) SalesYTD
FROM
(
valitse vuosi(SOH.OrderDate) SalesYear,
CAST (”Q” +CAST(DATEPART(neljännes, SOH.Järjestyksenpito) varchar(1)) AS VARCHAR(2)) neljännekset,
SOH.Välisumma Kokonaissaleina
myynnistä.SalesOrderHeader SOH
JOIN sales.Myyntitilauksetyksityiskohtainen SOD ON SOH.SalesOrderId = SOD.SalesOrderId
) Data PIVOT (SUM (TotalSales) for Quarters IN (,
,
)) AS pvt
ORDER BY SalesYear;
|
seuraava tuotos on myyntitietojen neljännesvuosittainen jako
esimerkki 4: how to get monthly sales using a SQL pivot Operation
let us take a look into another example to see sales split data based on every month. Seuraavassa esimerkissä in-lausekkeen arvot luodaan osana DATEPART-funktiota. DATEPART-arvot syötetään PIVOT-operaattorin lausekkeeseen.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
25
26
div>27 28
29
30
31
32
33
|
SELECT SalesYear,
Isnull(, 0) as Jan,
Isnull(, 0) as Feb,
Isnull(, 0) as Mar,
ISNULL(, 0) as huhti,
isnull(, 0) as huhti,
ISNULL(, 0) as Toukokuu,
isnull(, 0) kesäkuuksi,
isnull(, 0) heinäkuuksi,
isnull(, 0) elokuuksi,
isnull(, 0) syyskuuksi,
isnull(, 0) lokakuuksi,
isnull(, 0) lokakuuksi,
isnull (, 0) marraskuuksi,
isnull (, 0) marraskuuksi,
isnull (, 0) Dec,
(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
alkaen
(
valitse vuosi (SOH.Tilauspäivä) nimellä SalesYear,
DATEPART(KK, SOH.OrderDate) kuukautta,
SOH.Välisumma Kokonaissaleina
myynnistä.SalesOrderHeader SOH
JOIN sales.Myyntitilauksetyksityiskohtainen SOD ON SOH.SalesOrderId = SOD.SalesOrderId
), KOSKA Tietoja PIVOT – (SUM(TotalSales) Kuukausia(,
,
,
,
,
,
,
,
,
,
,
-)) KUIN pvt;
|
seuraava tuotos on kuukausittainen jako myyntitietoihin
esimerkki 5: Kuinka saada kuukausimyynti dynaamisella SQL pivot-operaatiolla
olkaamme käsitellään myyntitietojen kuukausittaista jakoa dynaamisella kääntymisellä. Keskustelimme staattisista kääntöoperaatioista. Muuttaaksemme staattisen kääntymisoperaation dynaamiseksi, meidän on poistettava kovakoodatut arvot lausekkeesta. Ensimmäinen, saada erillinen lauseke aineisto ja sitten valmistella merkkijono yhdistämällä kaikki lausekkeet. Seuraavassa esimerkissä @ – sarakenimeä käytetään kaikkien lausekkeiden yhdistämiseen. Tiivistetty lauseke syötetään pivotille lausekkeessa. Loput on yksinkertainen muuntaminen staattinen SQL osaksi dynaaminen SQL ja soita @DML käyttäen sp_executesql tallennettu menettely.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
25
26
div>27 |
toteamalla @DML nvarchar(Max)
julistettu @Columname AS NVARCHAR (MAX)
SELECT @Columname= ISNULL (@Columname + ’,’,”)
+ QUOTENAME (Months)
FROM(SELECT different DATEPART (MONTH, SOH.Tilauspäivä) kuukautta
myynnistä.SalesOrderHeader SOH
JOIN sales.Myyntitilauksetyksityiskohtainen SOD ON SOH.SalesOrderId = SOD.SalesOrderId
GROUP BY YEAR(SOH.Päiväys),
päiväys(kuukausi, SOH.OrderDate)) kuukausina
–valmista PIVOT-kysely käyttäen dynaamista
SET @DML =
n ’select SalesYear,’ +@Columname + ’ FROM
(
SELECT YEAR(SOH.Tilauspäivä) nimellä SalesYear,
DATEPART(KK, SOH.Tilauspäivä) kuukautta,
summa(SOH.Välisumma) summina
myynnistä.SalesOrderHeader SOH
JOIN sales.Myyntitilauksetyksityiskohtainen SOD ON SOH.SalesOrderId = SOD.SalesOrderId
GROUP BY YEAR(SOH.Päiväys),
päiväys(kuukausi, SOH.OrderDate)) AS t
PIVOT(SUM(TotalSales)
for Months IN (”+@Columname+”)) AS PVTTable”
–Print @DynamicPivotQuery
–Exec the Dynamic Pivot Query
|
that ’ s all for now…
wrap up
tähän mennessä olemme osoittaneet SQL pivot-ja SQL unpivot-peruskäsitteet ja useita esimerkkejä.
otos perustuu SQL Server adventureworks2014-tietokantaan. Voit kokeilla ja testata kaikki näytteet itse. Jos sinulla on kysyttävää, voit vapaasti kommentoida alla…
erikoisuuteni on suunnittelussa & korkean käytettävyyden ratkaisujen ja cross-platform DB-migraation toteuttaminen. Tällä hetkellä toimivia teknologioita ovat SQL Server, PowerShell, Oracle ja MongoDB.
View all posts by Prashanth Jayaram
- a quick overview of database audit in SQL – January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and On-Premises SQL Server – January 20, 2021
- How to perform Azure SQL database Import/Export operations using PowerShell – January 14, 2021