? I SQL är en pivot tabell en uppsättning data som omvandlas från en samling av separata rader till en samling kolumner . I relationsdatabaser , t.ex. Microsoft SQL Server , Oracle och MySQL kan pivottabeller användas för att förenkla omfattande data för att göra det lättare att läsa och förstå . För att skapa en pivottabell är ett aggregat som används mot en uppsättning data för att distribuera flera rader av en enda kolumn till en enda rad med flera kolumner . Detta uttrycks väsentligen resultatet inställd sidled . Sample Data
för att bättre förstå en pivot tabell , är ett exempel på några försäljningen uppgifter som anges här . Kopiera följande till Microsoft SQL Server Management Studio för att prova exemplen .
Skapa tabell # PivotTestTable ( CustName varchar ( 8 ) , ITEM_TYPE varchar ( 8 ) , Item_Amount numeriska ( 6,2 ) ) katalog
infoga i # PivotTestTableselect " Jason " , "dator" , 435.34unionselect " Jason " , "Programvara" , 243.54unionselect ' Jason ' , 'Monitor ' , 158.23unionselect " Alison " , "dator" , 345.89unionselect " Alison " , "Programvara" , 78.78unionselect " Alison " , 'Monitor ' , 123,45 Addera Starta UnPivoted Data
när temp tabellen , # PivotTestTable , frågas , resultatet är följande .
CustName ITEM_TYPE Item_Amount -------- -------------------- Alison Computer 345.89Alison Bildskärm 123.45Alison Software 78.78Jason Computer 435.34Jason bildskärm 158.23Jason Software 243,54
som ni kan se , visar resultatet set två kunder , Alison och Jason , som har köpt tre olika typer av objekt . Det finns sex rader med data för två kunder . Om vi ville se data i en enda rad per kund , skulle vi använda en pivottabell för att uppnå önskat resultat .
Pivot av pivotfunktion
Microsoft SQL Server har en pivot -funktion inbyggd i SQL Server . Här är ett exempel med # PivotTestTable uppgifter .
SELECTCustName som Total_Sales_By_Cust , dator, bildskärm , programvara på ( SELECTCustName , ITEM_TYPE , Item_AmountFROM # PivotTestTable ) aPIVOT ( sum ( Item_Amount ) FOR ITEM_TYPE i ( dator, bildskärm , mjukvara ) ) b på
Denna fråga återkommer de ursprungliga sex rader svängs in två rader med separata kolumner för varje typ som såldes av objektet . Resultatet som genereras från den här frågan är här :
Total_Sales_By_Cust Datorskärm Software ------------------- ----------- --------------- Alison 345,89 123,45 78.78Jason 435,34 158,23 243,54
Pivot genom aggregerad fallprogramsats
genom att använda ett aggregat funktion ( sUM , AVG , MIN , MAX ) kring ett fall uttalande i en SQL-fråga , kan vi uppnå samma resultat som pivotfunktion med mindre arbete .
SELECTCustName som Total_Sales_By_Cust , sum ( case ITEM_TYPE när " Dator " och sedan Item_Amount ände ) som Datorer , sum ( case ITEM_TYPE när 'Monitor ' då Item_Amount ände ) som bildskärm , sum ( case ITEM_TYPE när "Programvara" sedan Item_Amount ände ) som programvara på PivotTestTableGROUP # BY CustName
frågan kommer tillbaka exakt samma resultat som i det tidigare exemplet och är bara en preferens för vilken typ av pivot att använda .
vanligt misstag med pivottabeller
en vanlig misstaget att skapa en pivottabell är att skapa en koppling tillbaka till källan bordet . Detta kommer att ge otillförlitliga resultat och bör undvikas . Detta exempel är absolut ett exempel på vad man inte ska göra . Resultatet i detta prov kommer att vara samma , men detta prov inte kommer att fungera i alla fall
SELECTp1.CustName , p1.Item_Amount som Datorer , p2.Item_Amount som bildskärm , p3.Item_Amount som programvara på # PivotTestTable p1INNER . GÅ # PivotTestTable p2on p1.CustName = p2.CustNameand p2.Item_Type = ' Monitor'INNER GÅ # PivotTestTable p3on p1.CustName = p3.CustNameand p3.Item_Type = ' Software'WHERE p1.Item_Type = ' Computer '
Addera
