Inleiding
In mijn laatste artikel “Omzetten door Komma’ s Gescheiden Waarde te Rijen en Vice Versa in SQL Server”, sprak ik over hoe je het kunt zetten door komma ‘ s gescheiden (of gescheiden met een ander teken) waarden in één kolom in rijen en vice versa. In dit artikel laat ik zien hoe u rijen-waarden kunt converteren naar kolomwaarden (PIVOT) en kolomwaarden in rijen-waarden (UNPIVOT) in SQL Server.,
het converteren van rijen naar kolommen – PIVOT
SQL Server heeft een PIVOT relationele operator om de unieke waarden van een opgegeven kolom van meerdere rijen om te zetten in meerdere kolomwaarden in de uitvoer (cross-tab), effectief roteren van een tabel. Het maakt ook het uitvoeren van aggregaties, waar nodig, voor kolomwaarden die worden verwacht in de uiteindelijke output. De basis syntaxis voor een PIVOT relationele operator ziet er als volgt uit:
met het script hieronder maken we een tabel en laden we er wat gegevens in., Zoals u kunt zien in de afbeelding hieronder, het heeft verkoopinformatie voor sommige landen voor een paar jaar. Ook, als u merkt, voor elk land en voor elk jaar is er een aparte rij.
nu met behulp van de PIVOT operator, zullen we rijwaarden converteren naar kolomwaarden met het script hieronder en de resultaten zoals weergegeven in de afbeelding hieronder.
hoewel we de SOMAGGREGATIEFUNCTIE hebben gebruikt, is er in dit geval geen sommatie, omdat er slechts één rij is voor elke unieke combinatie voor land en jaar., Let op het gebruik van haakjes voor draaikolomwaarden; deze zijn vereist.
dynamisme naar de PIVOT
Als u merkt in het bovenstaande script, hebben we waarden (2005, 2006, 2007 en 2008) voor pivot kolommen gegeven omdat deze waarden beschikbaar zijn in de oorspronkelijke datasets. Maar wat als er in de toekomst een aantal extra waarden worden verwacht, bijvoorbeeld 2009 en 2010, enz. voor de draaikolom?,
in dat geval kunt u nog steeds de draaikolomwaarden gebruiken, die naar verwachting zullen komen (of die nog steeds niet beschikbaar zijn in de oorspronkelijke dataset) in de toekomst, hoewel u NULL zult zien voor zijn waarden. Het script hieronder toont dit scenario en de afbeelding hieronder toont NULLs voor de jaren 2009 en 2010 omdat er geen gegevens voor deze jaren.
de hierboven besproken aanpak werkt prima als u al alle mogelijke waarden voor de draaikolom kent, maar wat als u dat niet weet?,
In dat geval kunt u een dynamische query schrijven om eerst alle unieke waarden voor de pivot – kolom te pakken tijdens runtime en vervolgens een dynamische query schrijven om deze uit te voeren met de pivot-query zoals hieronder getoond:
kolommen naar rijen converteren-UNPIVOT
UNPIVOT is een andere relationele operator in SQL Server die bijna de omgekeerde bewerking van PIVOT uitvoert, door te roteren kolomwaarden in rijen waarden. Laat me dit demonstreren met een voorbeeld; laten we een tabel maken met draaiende gegevens uit de vorige query met het script hieronder., De afbeelding hieronder toont gegevens van de nieuw gemaakte tabel met draaiende gegevens.
om kolomwaarden in rijwaarden te roteren, gebruiken we nu de unpivot operator zoals hieronder getoond. De afbeelding hieronder toont geroteerde gegevens:
Als u terug verwijst, zei ik dat UNPIVOT bijna het omgekeerde is van de PIVOT operator; dit betekent dat het wel of niet precies hetzelfde kan zijn. De reden is dat Pivot aggregatie uitvoert tijdens het roteren van rijwaarden in kolomwaarden en mogelijk meerdere rijwaarden samenvoegt in één kolomwaarde in de uitvoer., Bijvoorbeeld, overwegen voor een bepaald land en jaar zijn er twee waarden, laten we zeggen 5000 en 6000. Wanneer u het nu draait, heeft de uitvoer 11000 als kolomwaarde als u som als aggregatiefunctie hebt. Later als je het terug wilt uitpakken, krijg je 11000 niet de bi-furcated waarden (5000 en 6000 als origineel). Dat gezegd hebbende, kunnen we zeggen dat als de pivoted waarden geaggregeerde waarden zijn, het niet mogelijk zal zijn om de originele gegevens terug te krijgen.,
conclusie
In dit artikel heb ik aangetoond hoe u rijwaarden kunt converteren naar kolomwaarden (PIVOT) en kolomwaarden naar rijwaarden (UNPIVOT) in SQL Server. Ik sprak ook over het schrijven van een dynamische query om een dynamische pivot query te schrijven waarin mogelijke pivot kolomwaarden worden bepaald tijdens runtime.