Wat is een draaitabel in SQL ?

Wat is een draaitabel in SQL ?

In SQL, een spillijst is een set van gegevens die is getransformeerd van een verzameling van aparte rijen naar een verzameling van columns.In relationele databases, zoals Microsoft SQL Server, Oracle en MySQL, kunnen draaitabellen worden gebruikt om uitgebreide gegevens te vereenvoudigen om het gemakkelijker maken om te lezen en te begrijpen.Om een ​​draaitabel te maken, wordt een aggregaat gebruikt tegen een set van gegevens naar meerdere rijen van een enkele kolom te verdelen in één rij met meerdere kolommen.Deze scharniert hoofdzakelijk zijwaarts ingesteld resultaat.

voorbeeld data

  • Om beter te begrijpen van een draaitabel, een voorbeeld van een aantal verkopen gegevens hier vermeld.Kopieer de volgende in Microsoft SQL Server Management Studio om de voorbeelden uit te proberen.

    Maak tafel #PivotTestTable
    (CustName varchar (8),
    ITEM_TYPE varchar (8),
    Item_Amount numerieke (6,2))

    invoegen in #PivotTestTable
    selecteer 'Jason', 'Computer', 435,34
    unie
    selecteer 'Jason', 'Software', 243,54
    unie
    selecteer 'Jason', 'Monitor', 158,23
    unie
    selecteer 'Alison', 'Computer', 345,89
    unie
    selecteer 'Alison', 'Software', 78,78
    unie
    selecteer 'Alison', 'Monitor', 123,45

Vanaf UnPivoted gegevens

  • Wanneer de temp tafel, #PivotTestTable, wordt opgevraagd, het resultaat is het volgende.

    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

    Zoals u kunt zien, het resultaat set toont twee klanten, Alison en Jason, diekocht drie verschillende soorten items.Er zijn zes rijen gegevens voor twee klanten.Als we wilden om de gegevens te zien in een enkele rij per klant, zouden we een draaitabel gebruiken om het gewenste resultaat te bereiken.

Pivot door PIVOT functie

  • Microsoft SQL Server heeft een PIVOT functie ingebouwd in SQL Server.Hier is een voorbeeld van de #PivotTestTable gegevens.

    SELECT

    CustName als Total_Sales_By_Cust,
    Computer,
    Monitor,
    Software

    VAN

    (
    SELECT
    CustName,
    ITEM_TYPE,
    Item_Amount
    VAN #PivotTestTable
    ) een

    PIVOT

    (
    sum (Item_Amount)
    VOOR ITEM_TYPE in(Computer, Monitor, Software)
    ) b

    Deze vraag zal de terugkeer van de oorspronkelijke zes rijen gedraaid in twee rijen met aparte kolommen voor elk type item verkocht.Het resultaat set gegenereerd van deze vraag is hier:

    Total_Sales_By_Cust Computer Monitor Software

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

Pivot door geaggregeerde Case Verklaring

  • Door het gebruik van eenstatistische functie (SUM, AVG, MIN, MAX) rond een case statement in een SQL-query, kunnen we hetzelfde resultaat als de PIVOT functie met minder werk te bereiken.

    SELECT

    CustName als Total_Sales_By_Cust,
    som (geval ITEM_TYPE als 'Computer' en vervolgens Item_Amount einde) als Computer,
    som (geval ITEM_TYPE als 'Monitor' dan Item_Amount einde) als Monitor,
    som (geval ITEM_TYPE als 'Software' en vervolgensItem_Amount end) als Software

    FROM #PivotTestTable
    GROUP BY CustName

    Deze vraag zal exact hetzelfde resultaat set van het vorige voorbeeld terug te keren en is slechts een voorkeur voor welk type scharnier te gebruiken.

veelgemaakte fout met Pivot Tables

  • Een veelgemaakte fout om een ​​draaitabel te maken is het creëren van een treden terug op de bron tafel.Dit onbetrouwbare resultaten en moet worden vermeden.Dit voorbeeld is strikt een voorbeeld van wat niet te doen.Het resultaat van dit monster zal hetzelfde zijn;maar dit voorbeeld werkt niet in alle gevallen.

    SELECT

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

    VAN #PivotTestTable p1
    INNER JOIN #PivotTestTable p2

    op p1.CustName = p2.CustName
    enp2.Item_Type = 'Monitor'

    INNER JOIN #PivotTestTable p3

    op p1.CustName = p3.CustName
    en p3.Item_Type = 'Software'

    WAAR p1.Item_Type = 'Computer'

148
0
5
Database Software