Hvad er en pivottabel i SQL ?

Hvad er en pivottabel i SQL ?

I SQL, en pivottabel er et sæt af data, der er forvandlet 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 datasæt til at distribuere flere rækker i en enkelt kolonne i en enkelt række med flere kolonner.Dette væsentlige drejer resultatet sat sidelæns.

Sample data

  • For bedre at forstå en pivottabel, et eksempel på nogle salg data, der er anført her.Kopier følgende 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))

    indsætte i #PivotTestTable
    vælge 'Jason', 'Computer', 435,34
    union
    vælg 'Jason', 'Software', 243,54
    union
    vælge 'Jason', 'Skærm', 158,23
    union
    vælge 'Alison', 'Computer', 345,89
    union
    vælge 'Alison', 'Software', 78,78
    union
    vælge 'Alison', 'Skærm', 123,45

Start UnPivoted data

  • Når temp tabellen, #PivotTestTable, forespørges, er resultatet følgende.

    CustName ITEM_TYPE Item_Amount

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

    Som du kan se, er resultatet sæt viser to kunder, Alison og Jason, som harindkøbt tre forskellige typer af elementer.Der er seks rækker af data for to kunder.Hvis vi ønskede at se data i en enkelt række per kunde, vil vi bruge en pivottabel at opnå det ønskede resultat.

Pivot ved PIVOT funktion

  • Microsoft SQL Server har en PIVOT funktion indbygget i SQL Server.Her er et eksempel med #PivotTestTable data.

    SELECT

    CustName som Total_Sales_By_Cust,
    Computer,
    Monitor,
    Software

    FRA

    (
    SELECT
    CustName,
    ITEM_TYPE,
    Item_Amount
    FRA #PivotTestTable
    ) en

    PIVOT

    (
    sum (Item_Amount)
    TIL ITEM_TYPE i(computer, skærm, software)
    ) b

    Denne forespørgsel vil returnere de oprindelige seks rækker drejes i to rækker med separate kolonner for hver type emne solgt.Resultatet sæt genereret fra denne forespørgsel er her:

    Total_Sales_By_Cust Computerskærm Software

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

Pivot af Aggregeret Case Statement

  • Ved at bruge ensamlede funktion (SUM, AVG, MIN, MAX) omkring en sag sætning i en SQL-forespørgsel, er vi i stand til at opnå det samme resultat som PIVOT funktion med mindre arbejde.

    SELECT

    CustName som Total_Sales_By_Cust,
    sum (sag ITEM_TYPE når 'Computer' og derefter Item_Amount ende) som Computer,
    sum (sag ITEM_TYPE når 'Skærm' derefter Item_Amount ende) som Monitor,
    sum (sag ITEM_TYPE når »Software«, derefterItem_Amount ende) som Software

    FRA #PivotTestTable
    GROUP BY CustName

    Denne forespørgsel vil returnere præcis samme resultat sæt det foregående eksempel og er kun en præference for, hvilken type pivot til at bruge.

Fælles Mistake med pivottabeller

  • En almindelig fejl 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 i alle tilfælde.

    SELECT

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

    FRA #PivotTestTable p1
    INNER JOIN #PivotTestTable p2

    på p1.CustName = p2.CustName
    ogp2.Item_Type = 'Skærm'

    INNER JOIN #PivotTestTable p3

    på p1.CustName = p3.CustName
    og p3.Item_Type = 'Software'

    WHERE p1.Item_Type = 'Computer'

664
0
5
Database Software