Hvad er en pivottabel i SQL ?

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'