Inledning
I min förra artikel ”Konvertera Comma Separated Value till Rader och Vice Versa i SQL Server”, och jag berättade om hur du kan konvertera åtskilda av kommatecken (eller separeras med några andra tecken) värden i en enda kolumn i rader och vice versa. I den här artikeln visar jag hur du kan konvertera rader värden till kolumner värden (PIVOT) och kolumner värden till rader värden (UNPIVOT) i SQL Server.,
konvertera rader till kolumner – PIVOT
SQL Server har en PIVOT relationsoperatör för att förvandla de unika värdena för en angiven kolumn från flera rader till flera kolumnvärden i utdata (cross-tab), effektivt rotera en tabell. Det gör det också möjligt att utföra aggregat, där så krävs, för kolumnvärden som förväntas i den slutliga produktionen. Den grundläggande syntaxen för en pivotrelationsoperatör ser ut så här:
med skriptet nedan, låt oss skapa en tabell och ladda lite data i den., Som du kan se i bilden nedan har den försäljningsinformation för vissa länder i några år. Dessutom, om du märker, för varje land och för varje år finns en separat rad.
nu genom att använda PIVOT-operatören konverterar vi radvärden till kolumnvärden med skriptet nedan och resultaten som visas i bilden nedan.
även om vi har använt SUM aggregering funktionen, i detta fall finns det ingen summering, eftersom det finns bara en rad för varje unik kombination för land och år., Observera användningen av parentes för pivotkolumnvärden; dessa krävs.
föra dynamik till PIVOT
om du märker i ovanstående skript, har vi tillhandahållit värden (2005, 2006, 2007 och 2008) för pivotkolumner eftersom dessa värden är tillgängliga i de ursprungliga datauppsättningarna. Men vad händer om några ytterligare värden förväntas komma i framtiden, till exempel 2009 och 2010 etc. för pivotkolonnen?,
i så fall kan du fortfarande använda pivotkolumnvärdena, som förväntas komma (eller som fortfarande inte är tillgängliga i den ursprungliga datauppsättningen) i framtiden även om du kommer att se NULL för dess värden. Skriptet nedan visar detta scenario och bilden nedan visar NULLs för åren 2009 och 2010 eftersom det inte finns några data för dessa år.
ovanstående tillvägagångssätt fungerar bra om du redan känner till alla möjliga värden för pivotkolumnen, men vad händer om du inte gör det?,
i så fall kan du skriva en dynamisk Fråga för att först ta alla unika värden för pivotkolumnen vid körning och sedan en skrivdynamisk Fråga för att utföra den med pivotfrågan som visas nedan:
konvertera kolumner till rader – UNPIVOT
UNPIVOT är en annan relationsoperatör i SQL Server som utför nästan omvänd drift av PIVOT, genom att rotera kolumnvärden i rader värden. Låt mig visa detta med ett exempel; låter skapa en tabell med pivoterade data från föregående fråga med skriptet nedan., Bilden nedan visar data från den nyskapade tabellen med pivoterade data.
för att rotera kolumnvärden till radvärden använder vi nu UNPIVOT-operatören som visas nedan. Bilden nedan visar roterade data:
om du hänvisar tillbaka, sa jag att UNPIVOT är nästan baksidan av PIVOT-operatören; det betyder att det kanske eller kanske inte är exakt samma. Anledningen är att Pivot utför aggregering medan roterande radvärden i kolumnvärden och kan sammanfoga möjliga flera radvärden till ett kolumnvärde i utmatningen., Tänk till exempel på ett visst land och år finns det två värden, säg 5000 och 6000. Nu när du vrider den kommer utmatningen att ha 11000 som kolumnvärde om du har summan som aggregeringsfunktion. Senare om du vill unpivot det tillbaka, kommer du att få 11000 inte bi-furcated värden (5000 och 6000 som original). Med detta sagt kan vi säga om de pivoterade värdena är aggregerade värden, kommer det inte att vara möjligt att få tillbaka de ursprungliga uppgifterna.,
slutsats
i den här artikeln visade jag hur du kan konvertera radvärden till kolumnvärden (PIVOT) och kolumnvärden till radvärden (UNPIVOT) i SQL Server. Jag pratade också om att skriva en dynamisk Fråga för att skriva en dynamisk pivotfråga där möjliga pivotkolumnvärden bestäms vid körning.