SQL Server PIVOT (Română)

Rezumat: în acest tutorial, veți afla cum să utilizați operatorul SQL Server PIVOT pentru a converti rândurile în coloane.

Configurarea obiectivelor

Pentru demonstrație, vom folosi production.products și production.categories tabele din baza de date eșantion:

Următoarea interogare găsește numărul de produse pentru fiecare categorie de produse:

Iată rezultatul:

Obiectivul nostru este să transformăm numele categoriilor din prima coloană a rezultatului în mai multe coloane și să numărăm numărul de produse pentru fiecare numele categoriei ca următoarea imagine:

În plus, putem adăuga anul model pentru a grupa categoria ory după model, după cum se arată în următorul rezultat:

Introducere în operatorul SQL Server PIVOT

Operatorul SQL Server PIVOT rotește o expresie cu valoare de tabel. Transformă valorile unice dintr-o coloană în coloane multiple în ieșire și efectuează agregări pentru toate valorile coloanei rămase.

Urmați acești pași pentru a face o interogare un tabel pivot:

• Mai întâi, selectați un set de date de bază pentru pivotare.
• În al doilea rând, creați un rezultat temporar utilizând un tabel derivat sau o expresie de tabel comun (CTE)
• În al treilea rând, aplicați PIVOT operator.

Să aplicăm acești pași în exemplul următor.

Mai întâi, selectați numele categoriei și codul produsului din production.products și production.categories tabele ca date de bază pentru pivotare:

În al doilea rând, creați un set de rezultate temporare utilizând un tabel derivat:

În al treilea rând, aplicați operatorul PIVOT:

Această interogare generează următorul rezultat:

Acum, orice coloană suplimentară pe care o adăugați la lista selectată a interogării care returnează baza datele vor forma automat grupuri de rânduri în tabelul pivot. De exemplu, puteți adăuga coloana modelului la interogarea de mai sus:

Iată rezultatul:

Generarea valorilor coloanei

În interogarea de mai sus, trebuia să tastați fiecare nume de categorie între paranteze după IN operator manual. Pentru a evita acest lucru, puteți utiliza funcția QUOTENAME() pentru a genera lista de nume de categorii și a le copia pe interogare.

Mai întâi, generați lista de nume de categorii:

Rezultatul va arăta astfel:

În acest fragment:

• Funcția QUOTENAME() înfășoară numele categoriei după paranteze pătrate, de ex.,
• Funcția LEFT() elimină ultima virgulă din șirul @columns .

În al doilea rând, copiați lista de nume de categorii din rezultat și lipiți-o în interogare.

Tabelele dinamice pivot

Dacă adăugați un nume de categorie nouă în tabelul production.categories, trebuie să rescrieți interogarea, ceea ce nu este ideal. Pentru a evita acest lucru, puteți utiliza SQL dinamic pentru a dinamiza tabelul pivot.

În această interogare, în loc să treceți o listă fixă de nume de categorii către PIVOT operator, construim lista de nume de categorii și o transmitem unei instrucțiuni SQL, apoi executăm această instrucțiune dinamic folosind procedura stocată sp_executesql.

În acest tutorial , ați învățat cum să utilizați tabelul SQL Server PIVOT pentru a converti rândurile în coloane.