Indledning
I min sidste artikel, “Konvertering Comma Separated Value til Rækker og Vice Versa i SQL Server”, jeg talte om, hvordan du kan konvertere komma-separeret (eller adskilt med nogle andre tegn) værdier i en enkelt kolonne i rækker og vice versa. I denne artikel demonstrerer jeg, hvordan du kan konvertere rækker værdier til kolonner værdier (PIVOT) og kolonner værdier i rækker værdier (UNPIVOT) I s .l Server.,
konvertering af rækker til kolonner – PIVOT
S .l Server har en PIVOT relationel operatør til at vende de unikke værdier af en specificeret kolonne fra flere rækker til flere kolonneværdier i output (cross-tab), effektivt at rotere en tabel. Det giver også mulighed for at udføre aggregeringer, hvor det er nødvendigt, for kolonneværdier, der forventes i den endelige output. Den grundlæggende syntaks for en PIVOT-relationel operatør ser sådan ud:
med scriptet nedenfor, lad os oprette en tabel og indlæse nogle data i den., Som du kan se på billedet nedenfor, har det salgsoplysninger for nogle lande i et par år. Også, hvis du bemærker, for hvert land og for hvert år er der en separat række.
nu ved at bruge PIVOT-operatøren konverterer vi rækkeværdier til kolonneværdier med scriptet nedenfor og resultaterne som vist på billedet nedenfor.
selvom vi har brugt SUM aggregation-funktionen, er der i dette tilfælde ingen summation, da der kun er en række for hver unik kombination for land og år., Bemærk brugen af parenteser til pivot kolonneværdier; disse er påkrævet.
Bringe Dynamik til PIVOT
Hvis du opdager, i ovenstående script, vi har fastsat værdier (2005, 2006, 2007 og 2008) for pivot søjler, da disse værdier er tilgængelige i det oprindelige datasæt. Men hvad nu hvis nogle yderligere værdier forventes at komme i fremtiden, for eksempel 2009 og 2010 osv. for pivot kolonnen?,
i dette tilfælde kan du stadig bruge pivot-kolonneværdierne, som forventes at komme (eller som stadig ikke er tilgængelige i det originale datasæt) i fremtiden, selvom du vil se NULL for dets værdier. Scriptet nedenfor viser dette scenario, og billedet nedenfor viser nuller for årene 2009 og 2010, da der ikke er nogen data for disse år.
ovenstående diskuterede tilgang fungerer fint, hvis du allerede kender alle de mulige værdier for pivot-kolonnen, men hvad hvis du ikke gør det?,
I dette tilfælde, kan du skrive en dynamisk forespørgsel til først at få fat i alle de unikke værdier for pivot kolonne på runtime og derefter skrive en dynamisk forespørgsel til at udføre det med pivot forespørgsel, som vist nedenfor:
Konvertering Kolonner til Rækker – UNPIVOT
UNPIVOT er en anden relationel operator i SQL Server, der udfører næsten den omvendte operation af PIVOT, ved at dreje kolonne af værdier i rækker værdier. Lad mig demonstrere dette med et eksempel; lad os oprette en tabel med drejelige data fra den forrige forespørgsel med scriptet nedenfor., Billedet nedenfor viser data for den nyoprettede tabel med drejelige data.
for at rotere kolonneværdier til rækkeværdier bruger vi nu unpivot-operatøren som vist nedenfor. Billedet nedenfor viser roterede data:
Hvis du henviser tilbage, sagde jeg, at UNPIVOT er næsten det modsatte af PIVOT-operatøren; dette betyder, at det måske eller måske ikke er nøjagtigt det samme. Årsagen er, Pivot udfører aggregering, mens roterende rækkeværdier i kolonneværdier og kan fusionere mulige flere rækkeværdier i enkelt kolonneværdi i output., For eksempel overveje for et givet land og år er der to værdier, siger 5000 og 6000. Nu når du drejer det, vil udgangen have 11000 som kolonneværdi, hvis du har SUM som aggregeringsfunktion. Senere, hvis du ønsker at unpivot det tilbage, vil du få 11000 ikke de bi-furcated værdier (5000 og 6000 som original). Når det er sagt, kan vi sige, at hvis de drejelige værdier er aggregerede værdier, vil det ikke være muligt at få de originale data tilbage.,
konklusion
i denne artikel demonstrerede jeg, hvordan du kan konvertere rækkeværdier til kolonneværdier (PIVOT) og kolonneværdier til rækkeværdier (UNPIVOT) i s .l Server. Jeg talte også om at skrive en dynamisk forespørgsel for at skrive en dynamisk pivotforespørgsel, hvor mulige pivot-kolonneværdier bestemmes ved kørsel.