PIVOT di SQL Server

Riepilogo: in questo tutorial imparerai come utilizzare loperatore PIVOT di SQL Server per convertire le righe in colonne.

Impostazione degli obiettivi

Per la dimostrazione, utilizzeremo production.products e production.categories dal database di esempio:

La seguente query trova il numero di prodotti per ciascuna categoria di prodotto:

Ecco loutput:

Il nostro obiettivo è trasformare i nomi delle categorie dalla prima colonna delloutput in più colonne e contare il numero di prodotti per ciascuna nome della categoria come la seguente immagine:

Inoltre, possiamo aggiungere lanno modello per raggruppare il categ ory per anno del modello come mostrato nel seguente output:

Introduzione alloperatore PIVOT di SQL Server

Loperatore PIVOT di SQL Server ruota unespressione con valori di tabella. Trasforma i valori univoci in una colonna in più colonne nelloutput ed esegue aggregazioni su tutti i valori di colonna rimanenti.

Segui questi passaggi per rendere una query una tabella pivot:

• Per prima cosa, seleziona un set di dati di base per il pivot.
• Secondo, crea un risultato temporaneo utilizzando una tabella derivata o unespressione di tabella comune (CTE)
• Terzo, applica il PIVOT operatore.

Applichiamo questi passaggi nel seguente esempio.

Per prima cosa, seleziona il nome della categoria e lID del prodotto dal production.products e production.categories come dati di base per il pivot:

Secondo, crea un set di risultati temporaneo utilizzando una tabella derivata:

Terzo, applica loperatore PIVOT:

Questa query genera il seguente output:

Ora, qualsiasi colonna aggiuntiva aggiunta allelenco di selezione della query che restituisce il base i dati formeranno automaticamente gruppi di righe nella tabella pivot. Ad esempio, puoi aggiungere la colonna dellanno modello alla query precedente:

Ecco loutput:

Generazione di valori di colonna

Nella query precedente, dovevi digitare ogni nome di categoria tra parentesi dopo IN operatore manualmente. Per evitare ciò, puoi utilizzare la funzione QUOTENAME() per generare lelenco dei nomi delle categorie e copiarli sulla query.

Innanzitutto, genera lelenco dei nomi delle categorie:

Loutput sarà simile a questo:

In questo frammento:

• La funzione QUOTENAME() racchiude il nome della categoria tra parentesi quadre, ad es.
• La funzione LEFT() rimuove lultima virgola dalla stringa @columns .

In secondo luogo, copia lelenco dei nomi delle categorie dalloutput e incollalo nella query.

Tabelle pivot dinamiche

Se aggiungi un nuovo nome di categoria nella tabella production.categories, è necessario riscrivere la query, il che non è lideale. Per evitare di farlo, puoi utilizzare SQL dinamico per rendere dinamica la tabella pivot.

In questa query, invece di passare un elenco fisso di nomi di categoria a PIVOT, costruiamo lelenco dei nomi delle categorie e lo passiamo a unistruzione SQL, quindi eseguiamo questa istruzione dinamicamente utilizzando la procedura memorizzata sp_executesql.

In questo tutorial , hai imparato a utilizzare la tabella PIVOT di SQL Server per convertire le righe in colonne.