Innledning
I min forrige artikkel «Konvertering Kommadelt å Rader og Vice Versa i SQL Server», jeg snakket om hvordan du kan konvertere atskilt med komma (eller adskilt med noen andre tegn) verdier i en enkelt kolonne i rader og vice versa. I denne artikkelen, jeg demonstrere hvordan du kan konvertere rader verdier i kolonnene verdier (PIVOT) og kolonner verdier i rader verdier (UNPIVOT) i SQL Server.,
Konvertering Rader til Kolonner – PIVOT
SQL Server har en PIVOT relasjonelle operatør for å slå den unike verdier av en bestemt kolonne fra flere rader i flere kolonner verdier i utgang (cross-fanen), effektivt roterende bord. Det gjør det også utføre grupper celler, der det trengs, for kolonne med verdier som er forventet i den endelige utgang. Den grunnleggende syntaks for en PIVOT relasjonelle operatøren ser ut som dette:
Med skriptet nedenfor, la oss lage en tabell og legge data inn i det., Som du kan se i bildet nedenfor, det har salg opplysninger for enkelte land i et par år. Også, hvis du legger merke til, for hvert land og for hvert år det er en egen rad.
Nå ved hjelp av PIVOT operatør, vil vi konverterer rad verdier i kolonnen verdier med skriptet nedenfor og resultatene som vist i bildet nedenfor.
selv Om vi har brukt SUMMEN aggregering funksjon, i dette tilfellet er det ikke summering, så er det bare én rad for hver av de unike kombinasjon for land og år., Vær oppmerksom på at bruk av braketter for pivot kolonne verdier; disse er nødvendig.
Bringe Dynamikk til PIVOT
Hvis du legger merke til i skriptet ovenfor, har vi gitt verdier (2005, 2006, 2007 og 2008) for pivot kolonner som disse verdiene er tilgjengelige i den opprinnelige datasett. Men hva hvis noen ytterligere verdier er forventet å komme i fremtiden, for eksempel i 2009 og 2010, etc. for pivot kolonne?,
I det tilfelle, kan du fremdeles bruke pivot kolonne verdier, som er forventet å komme (eller som ennå ikke er tilgjengelige i det opprinnelige datasettet) i fremtiden om du vil se NULL for sine verdier. Skriptet nedenfor viser dette scenariet, og bildet nedenfor viser Nuller for årene 2009 og 2010 som det finnes ingen data for disse årene.
Den ovenfor diskuterte tilnærming fungerer fint hvis du allerede vet alle mulige verdier for pivot kolonne, men hva hvis du ikke gjør det?,
I det tilfelle, kan du skrive en dynamisk spørring til først å ta tak i alle de unike verdiene for pivot-kolonnen på kjøring, og deretter skrive en dynamisk spørring for å utføre det med pivot spørring som vist nedenfor:
Konvertering Kolonner og Rader – UNPIVOT
UNPIVOT er en annen relasjonelle operatør i SQL Server som utfører nesten den motsatte operasjonen av PIVOT, ved å rotere kolonne verdier i rader verdier. La meg vise dette med et eksempel, kan du opprette en tabell med svinges data fra den forrige spørring med skriptet nedenfor., Bildet nedenfor viser data for den nyopprettede tabell med svinges data.
for Å rotere kolonne verdier i rad verdier, vil vi nå bruke UNPIVOT operatør som vist nedenfor. Bildet nedenfor viser rotert data:
Hvis du vil se tilbake, sa jeg UNPIVOT er nesten det motsatte av PIVOT operatør; dette betyr at det kanskje eller kanskje ikke være nøyaktig de samme. Grunnen til dette er, Pivot utfører aggregering mens du roterer rad verdier i kolonnen verdier og kan slå sammen flere mulige rad verdier i én kolonne verdi i resultatet., For eksempel, tenk for et gitt land og år det er to verdier, sier 5000 og 6000. Når du nå pivot det, produksjonen vil ha 11000 som kolonnen verdi hvis du har SUMMEN som aggregering funksjon. Senere, hvis du ønsker å unpivot det tilbake, og du vil få 11000 ikke bi-furcated verdier (5000 og 6000 som original). Når det er sagt, kan vi si at hvis det svinges verdier er aggregerte verdier, vil det ikke være mulig å få de opprinnelige dataene tilbake.,
Konklusjon
I denne artikkelen, jeg demonstrerer hvordan du kan konvertere rad verdier i kolonnen verdier (PIVOT) og kolonne verdier i rad verdier (UNPIVOT) i SQL Server. Jeg har også snakket om å skrive en dynamisk spørring til å skrive en dynamisk pivot spørring som mulig pivot kolonne verdier bestemmes ved kjøring.