SQL Server PIVOT

Samenvatting: in deze tutorial leert u hoe u de SQL Server PIVOT -operator gebruikt om rijen naar kolommen te converteren.

De doelen instellen

Voor de demonstratie gebruiken we de production.products en production.categories tabellen uit de voorbeelddatabase:

Met de volgende zoekopdracht wordt het aantal producten voor elke productcategorie gevonden:

Hier is de uitvoer:

Ons doel is om de categorienamen uit de eerste kolom van de uitvoer om te zetten in meerdere kolommen en het aantal producten voor elk te tellen categorienaam als de volgende afbeelding:

Bovendien kunnen we het modeljaar toevoegen om de categ ory per modeljaar, zoals weergegeven in de volgende uitvoer:

Inleiding tot SQL Server PIVOT-operator

SQL Server PIVOT -operator roteert een expressie met tabelwaarde. Het verandert de unieke waarden in één kolom in meerdere kolommen in de uitvoer en voert aggregaties uit op alle resterende kolomwaarden.

U volgt deze stappen om van een query een draaitabel te maken:

• Selecteer eerst een basisdataset om te draaien.
• Ten tweede, maak een tijdelijk resultaat door een afgeleide tabel of een gemeenschappelijke tabelexpressie (CTE) te gebruiken.
• Ten derde, pas de PIVOT operator.

Laten we deze stappen toepassen in het volgende voorbeeld.

Selecteer eerst de categorienaam en product-ID uit de production.products en production.categories tabellen als de basisgegevens voor draaien:

Ten tweede, maak een tijdelijke resultatenset met behulp van een afgeleide tabel:

Ten derde, pas de PIVOT operator toe:

Deze query genereert de volgende output:

Nu, elke extra kolom die u toevoegt aan de selectielijst van de query die de baseren gegevens vormen automatisch rijgroepen in de draaitabel. U kunt bijvoorbeeld de modeljaarkolom aan de bovenstaande zoekopdracht toevoegen:

Hier is de uitvoer:

Kolomwaarden genereren

In de bovenstaande zoekopdracht moest je elke categorienaam tussen haakjes typen na de IN operator handmatig. Om dit te voorkomen, kunt u de functie QUOTENAME() gebruiken om de lijst met categorienamen te genereren en deze over de zoekopdracht te kopiëren.

Genereer eerst de lijst met categorienamen:

De uitvoer zal er als volgt uitzien:

In dit fragment:

• De QUOTENAME() functie omhult de categorienaam door vierkante haken, bijv.
• De functie LEFT() verwijdert de laatste komma uit de @columns-reeks .

Ten tweede, kopieer de categorienamenlijst uit de uitvoer en plak deze in de zoekopdracht.

Dynamische draaitabellen

Als u een nieuwe categorienaam naar de production.categories -tabel, moet u uw zoekopdracht herschrijven, wat niet ideaal is. Om dit te vermijden, kunt u dynamische SQL gebruiken om de draaitabel dynamisch te maken.

In deze zoekopdracht, in plaats van een vaste lijst met categorienamen door te geven aan de PIVOT operator, construeren we de categorienamenlijst en geven deze door aan een SQL-instructie, en voeren deze instructie vervolgens dynamisch uit met behulp van de opgeslagen procedure sp_executesql.

In deze tutorial , hebt u geleerd hoe u de SQL Server PIVOT -tabel gebruikt om rijen naar kolommen te converteren.