Introduzione
Nel mio ultimo articolo “la Conversione di valori Separati da una Virgola a Righe e Viceversa in SQL Server”, ho parlato di come è possibile convertire separati da virgola (o separati con altri caratteri) valori in un’unica colonna in righe e viceversa. In questo articolo, dimostro come è possibile convertire i valori di righe in valori di colonne (PIVOT) e valori di colonne in valori di righe (UNPIVOT) in SQL Server.,
Conversione di righe in colonne – PIVOT
SQL Server dispone di un operatore relazionale PIVOT per trasformare i valori univoci di una colonna specificata da più righe in più valori di colonna nell’output (scheda incrociata), ruotando efficacemente una tabella. Consente inoltre di eseguire aggregazioni, laddove richiesto, per i valori di colonna previsti nell’output finale. La sintassi di base per un operatore relazionale PIVOT si presenta così:
Con lo script qui sotto, creiamo una tabella e carichiamo alcuni dati in essa., Come puoi vedere nell’immagine qui sotto, ha informazioni di vendita per alcuni paesi per un paio d’anni. Inoltre, se si nota, per ogni paese e per ogni anno c’è una riga separata.
Ora utilizzando l’operatore PIVOT, convertiremo i valori di riga in valori di colonna con lo script indicato di seguito e i risultati come mostrato nell’immagine qui sotto.
Sebbene abbiamo usato la funzione di aggregazione SOMMA, in questo caso non esiste una sommatoria, poiché c’è solo una riga per ogni combinazione unica per paese e anno., Si prega di notare l’uso di parentesi per i valori delle colonne pivot; questi sono obbligatori.
Portare dinamismo al PIVOT
Se si nota nello script di cui sopra, abbiamo fornito valori (2005, 2006, 2007 e 2008) per le colonne pivot in quanto questi valori sono disponibili nei set di dati originali. Ma cosa succede se alcuni valori aggiuntivi dovrebbero arrivare in futuro, ad esempio 2009 e 2010, ecc. per la colonna pivot?,
In tal caso, è comunque possibile utilizzare i valori della colonna pivot, che dovrebbero venire (o che non sono ancora disponibili nel set di dati originale) in futuro, anche se vedrai NULL per i suoi valori. Lo script qui sotto mostra questo scenario e l’immagine qui sotto mostra NULL per gli anni 2009 e 2010 in quanto non ci sono dati per questi anni.
L’approccio sopra discusso funziona bene se conosci già tutti i possibili valori per la colonna pivot, ma cosa succede se non lo fai?,
In questo caso, è possibile scrivere una query dinamica per prima cosa prendere tutti i valori univoci per la colonna pivot in fase di runtime e una scrittura dinamica di esecuzione di una query con il perno di query come mostrato di seguito:
la Conversione delle Colonne per Righe – UNPIVOT
UNPIVOT è un altro operatore relazionale di SQL Server che esegue quasi l’opposto operazione di PIVOT, ruotando valori di colonna in righe di valori. Permettetemi di dimostrarlo con un esempio; consente di creare una tabella con dati imperniati dalla query precedente con lo script sottostante., L’immagine seguente mostra i dati della tabella appena creata con i dati imperniati.
Per ruotare i valori delle colonne in valori di riga, useremo ora l’operatore UNPIVOT come mostrato di seguito. L’immagine qui sotto mostra i dati ruotati:
Se fai riferimento indietro, ho detto che UNPIVOT è quasi il contrario dell’operatore PIVOT; questo significa che potrebbe o potrebbe non essere esattamente lo stesso. Il motivo è che Pivot esegue l’aggregazione mentre ruota i valori di riga in valori di colonna e potrebbe unire possibili valori di riga multipli in un singolo valore di colonna nell’output., Ad esempio, considera per un dato paese e anno ci sono due valori, diciamo 5000 e 6000. Ora quando lo fai ruotare, l’output avrà 11000 come valore della colonna se hai SUM come funzione di aggregazione. Successivamente, se si desidera annullarlo, si otterranno 11000 non i valori bi-furcati (5000 e 6000 come originali). Detto questo, possiamo dire che se i valori pivotati sono valori aggregati, non sarà possibile recuperare i dati originali.,
Conclusione
In questo articolo, ho dimostrato come è possibile convertire i valori di riga in valori di colonna (PIVOT) e valori di colonna in valori di riga (UNPIVOT) in SQL Server. Ho anche parlato di scrivere una query dinamica per scrivere una query pivot dinamica in cui i possibili valori della colonna pivot sono determinati in fase di esecuzione.