? I SQL, er en pivot tabell et sett med data som er transformert fra en samling av egne rader til en samling av kolonner . I relasjonsdatabaser , for eksempel Microsoft SQL Server , Oracle og MySQL kan pivot tabeller brukes til å forenkle omfattende data for å gjøre det enklere å lese og forstå. For å opprette en pivottabell , er et aggregat brukes mot et sett av data for å distribuere flere rader med en enkelt kolonne i en enkelt rad med flere kolonner . Dette egentlig dreier resultatet satt sidelengs . Eksempeldata
For bedre å forstå en pivot tabell , er et eksempel på noen salgsdata oppført her. Kopier følgende inn i Microsoft SQL Server Management Studio for å prøve ut eksempler.
Lag tabell # PivotTestTable ( CustName varchar ( 8 ) , ITEM_TYPE varchar ( 8 ) , Item_Amount numerisk ( 6,2 ) )
< p > Sett inn # PivotTestTableselect ' Jason ", " Computer " , 435.34unionselect ' Jason ',' Software ' , 243.54unionselect ' Jason ' , ' Skjerm ' , 158.23unionselect ' Alison ", "Computer" , 345.89unionselect ' Alison ' , ' Software' , 78.78unionselect ' Alison ' , 'Skjerm' , 123.45
Starter UnPivoted data
Når temp tabellen , # PivotTestTable , er spørres , er resultatet følgende .
CustName ITEM_TYPE Item_Amount -------- -------------------- Alison Computer 345.89Alison Monitor 123.45Alison programvare 78.78Jason datamaskin 435.34Jason Monitor 158.23Jason programvare 243,54
p Som du kan se, viser resultatet sett to kunder , Alison og Jason , som har kjøpt tre forskjellige typer elementer. Det er seks rader med data for to kunder. Hvis vi ønsket å se dataene i en enkelt rad per kunde, vil vi bruke en pivot tabell for å oppnå ønsket resultat .
Pivot av pivot-funksjon
< p > Microsoft SQL Server har en pivot-funksjon innebygd i SQL Server. Her er et eksempel med # PivotTestTable data .
SELECTCustName som Total_Sales_By_Cust , datamaskin, skjerm , SoftwareFROM ( SELECTCustName , ITEM_TYPE , Item_AmountFROM # PivotTestTable ) aPIVOT ( sum ( Item_Amount ) FOR ITEM_TYPE i ( PC, skjerm , programvare) ) b
Dette søket vil returnere de opprinnelige seks rader svinges inn i to rader med egne kolonner for hver type element solgt . Resultatet satt genereres fra denne spørringen er her :
Total_Sales_By_Cust dataskjerm programvare ------------------- ----------- --------------- Alison 345,89 123,45 78.78Jason 435,34 158,23 243,54
Pivot etter Aggregerte sak Statement
ved å bruke et aggregat funksjon ( SUM , AVG, MIN, MAX ) rundt en sak uttalelse i en SQL-spørring , er vi i stand til å oppnå samme resultat som pivot-funksjon med mindre arbeid .
SELECTCustName som Total_Sales_By_Cust , sum (case ITEM_TYPE når "Computer" og deretter Item_Amount enden) som Computer , sum (case ITEM_TYPE når " Monitor " og deretter Item_Amount enden) som Monitor , sum (case ITEM_TYPE når "Software " og deretter Item_Amount enden) som SoftwareFROM # PivotTestTableGROUP BY CustName
spørringen returnerer nøyaktig samme resultat sett med det forrige eksemplet , og er bare en preferanse for hvilken type pivot å bruke.
vanlig feil med pivot tabeller
en vanlig feil for å skape en dreietapp tabell er å skape en kombinering tilbake på kilden tabellen. Dette vil gi upålitelige resultater og bør unngås . Dette eksemplet er strengt et eksempel på hva som ikke skal gjøre. Resultatet i dette utvalget vil være den samme , men denne prøven ikke vil fungere i alle tilfeller
SELECTp1.CustName , p1.Item_Amount som Computer, p2.Item_Amount som Monitor, p3.Item_Amount som SoftwareFROM # PivotTestTable p1INNER . BLI # PivotTestTable p2on p1.CustName = p2.CustNameand p2.Item_Type = ' Monitor'INNER BLI # PivotTestTable p3on p1.CustName = p3.CustNameand p3.Item_Type = ' Software'WHERE p1.Item_Type = "Computer"
