? I SQL er en pivottabel et sæt data , der er omdannet fra en samling af separate rækker til en samling af kolonner. I relationelle databaser , såsom Microsoft SQL Server , Oracle og MySQL kan pivottabeller bruges til at forenkle omfattende data for at gøre det lettere at læse og forstå. For at oprette en pivottabel er et aggregat anvendes mod et sæt af data til at distribuere flere rækker i en enkelt kolonne i en enkelt række med flere spalter . Dette væsentlige drejer resultatet sat sidelæns. Sample data
til bedre at forstå en pivottabel er et eksempel på nogle salgsdata anført her. Kopier følgende ind i Microsoft SQL Server Management Studio til at afprøve eksemplerne.
Opret tabel # PivotTestTable ( CustName varchar (8) , ITEM_TYPE varchar (8) , Item_Amount numerisk ( 6,2 ) )
< p> insert into # PivotTestTableselect ' Jason ', ' Computer ' , 435.34unionselect ' Jason ', ' Software ' , 243.54unionselect ' Jason ', ' skærm' , 158.23unionselect ' Alison ', ' Computer ' , 345.89unionselect ' Alison ' "Software" , 78.78unionselect ' Alison ', ' skærm' , 123,45
Start UnPivoted data
Når temp tabel , # PivotTestTable , tvivl, er resultatet det følgende.
CustName ITEM_TYPE Item_Amount -------- -------------------- Alison Computer 345.89Alison Monitor 123.45Alison Software 78.78Jason computer 435.34Jason Monitor 158.23Jason Software 243,54
som du kan se , er resultatet set viser to kunder, Alison og Jason , der har købt tre forskellige typer af poster. Der er seks rækker af data for to kunder . Hvis vi ønskede at se data i en enkelt række per kunde , ville vi bruge en pivottabel til at opnå det ønskede resultat.
Pivot PIVOT funktion
Microsoft SQL Server har en PIVOT funktion indbygget i SQL Server. Her er et eksempel med # PivotTestTable data.
SELECTCustName som Total_Sales_By_Cust , Computer, Monitor , SoftwareFROM ( SELECTCustName , ITEM_TYPE , Item_AmountFROM # PivotTestTable ) aPIVOT (sum ( Item_Amount ) FOR ITEM_TYPE i ( computer, skærm , software ) ) b
Denne forespørgsel returnerer de oprindelige seks rækker svinges i to rækker med separate kolonner for hvert solgt emnetype . Resultatet sæt genereres fra denne forespørgsel er her : Hej
Total_Sales_By_Cust Computerskærm Software ------------------- ----------- --------------- Alison 345,89 123,45 78.78Jason 435,34 158,23 243,54
pivot aggregeret Case Statement
ved at bruge en samlet funktionen (SUM , AVG , MIN, MAX) omkring en sag erklæring i en SQL-forespørgsel , vi er i stand til at opnå det samme resultat som pIVOT funktion med mindre arbejde .
SELECTCustName som Total_Sales_By_Cust , sum (case ITEM_TYPE når 'computer' og derefter Item_Amount ende) som Computer, sum (case ITEM_TYPE når 'Skærm ' og derefter Item_Amount ende) som Monitor , sum (case ITEM_TYPE når "Software" og derefter Item_Amount ende) som SoftwareFROM # PivotTestTableGROUP BY CustName
forespørgsel vil returnere præcis samme resultat sæt af den foregående eksempel og er kun en præference for , hvilken type pivot at bruge.
fælles Mistake med pivottabeller
en fælles fejltagelse at oprette en pivottabel er at skabe en slutte tilbage på kilden bordet. Dette vil give upålidelige resultater, og bør undgås . Dette eksempel er strengt et eksempel på, hvad man ikke skal gøre . Resultatet i denne prøve vil være den samme , men denne prøve vil ikke arbejde i alle tilfælde
SELECTp1.CustName , p1.Item_Amount som Computer, p2.Item_Amount som Monitor , p3.Item_Amount som SoftwareFROM # PivotTestTable p1INNER . JOIN # PivotTestTable p2on p1.CustName = p2.CustNameand p2.Item_Type = ' Monitor'INNER JOIN # PivotTestTable p3on p1.CustName = p3.CustNameand p3.Item_Type =' Software'WHERE p1.Item_Type = ' Computer '
< br >
