Hva er en Pivot tabell i SQL?

Hva er en Pivot tabell i SQL?


I SQL, er en pivottabell et sett med data som er forvandlet fra en samling av separate rader til en samling av kolonner. I relasjonsdatabaser, for eksempel Microsoft SQL Server, Oracle og MySQL, kan pivottabeller kan brukes for å forenkle omfattende data for å gjøre det lettere å lese og forstå. For å opprette en dreiebordet, blir et aggregat brukes mot et sett av data for å fordele flere rader i en enkelt kolonne i en enkelt rad med flere kolonner. Dette egentlig dreier resultatet sett sidelengs.

eksempeldata

Hva er en Pivot tabell i SQL?


For bedre å forstå en pivottabell, er et eksempel på noen salgsdata her. Kopier følgende til Microsoft SQL Server Management Studio for å prøve ut eksemplene.

Opprette tabellen #PivotTestTable
(CustName varchar (8),
ITEM_TYPE varchar (8),
Item_Amount numerisk (6,2))

Sett inn #PivotTestTable
velg "Jason", "Computer", 435,34
union
velg 'Jason', 'Software', 243,54
union
velg 'Jason', 'Skjerm', 158,23
union
velg "Alison", "Computer", 345,89
union
velg "Alison ',' Software ', 78,78
union
velg "Alison ',' Skjerm ', 123,45

Starter UnPivoted data

Når temp bordet, #PivotTestTable, skal forespørres, er resultatet det følgende.

CustName ITEM_TYPE Item_Amount


Alison Computer 345,89
Alison Monitor 123,45
Alison Programvare 78,78
Jason Computer 435,34
Jason Monitor 158,23
Jason Programvare 243,54

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 pivottabell for å oppnå ønsket resultat.

Drei etter PIVOT funksjon

Hva er en Pivot tabell i SQL?


Microsoft SQL Server har en PIVOT funksjon innebygd i SQL Server. Her er et eksempel med #PivotTestTable data.

VELGE

CustName as Total_Sales_By_Cust,
Computer,
Monitor,
Software

FRA

(
SELECT
CustName,
Item_Type,
Item_Amount
FROM #PivotTestTable
) a

PIVOT

(
sum(Item_Amount)
FOR Item_Type in (Computer, Monitor,Software)
) b

Dette søket vil returnere de opprinnelige seks rader svinges inn i to rader med egne kolonner for hver type element solgt. Resultatsettet genereres fra denne spørringen er her:

Total_Sales_By_Cust dataskjerm programvare


Alison 345,89 123,45 78,78
Jason 435,34 158,23 243,54

Drei etter Aggregert sak Statement

Hva er en Pivot tabell i SQL?


Ved å bruke en samlefunksjon (SUM, AVG, MIN, MAX) rundt en sak uttalelse i en SQL-spørring, er vi i stand til å oppnå samme resultat som dreiefunksjon med mindre arbeid.

VELGE

CustName as Total_Sales_By_Cust,
sum(case Item_Type when 'Computer' then Item_Amount end) as Computer,
sum(case Item_Type when 'Monitor' then Item_Amount end) as Monitor,
sum(case Item_Type when 'Software' then Item_Amount end) as Software

Fra #PivotTestTable
GROUP BY CustName

Dette søket vil returnere nøyaktig samme resultat sett med forrige eksempel, og er bare en preferanse for hvilken type pivot å bruke.

Vanlig feil med pivottabeller

Hva er en Pivot tabell i SQL?


En vanlig feil å opprette en pivottabell er å skape en bli med tilbake på kildetabellen. Dette vil gi upålitelige resultater, og bør unngås. Dette eksempelet er strengt et eksempel på hva som ikke skal gjøre. Resultatet i denne prøven vil være den samme; men dette utvalget ikke vil fungere i alle tilfeller.

VELGE

p1.CustName,
p1.Item_Amount as Computer,
p2.Item_Amount as Monitor,
p3.Item_Amount as Software

Fra #PivotTestTable p1
INNER JOIN #PivotTestTable p2

on p1.CustName = p2.CustName
and p2.Item_Type = 'Monitor'

INNER JOIN #PivotTestTable p3

on p1.CustName = p3.CustName
and p3.Item_Type = 'Software'

HVOR p1.Item_Type = 'datamaskin'