Che cosa è una tabella pivot in SQL

? In SQL , una tabella pivot è un insieme di dati che viene trasformato da un insieme di righe separate da una raccolta di colonne . Nei database relazionali , quali Microsoft SQL Server , Oracle e MySQL , tabelle pivot possono essere utilizzati per semplificare i dati estesi al fine di facilitare la lettura e la comprensione . Per creare una tabella pivot, un aggregato viene usata contro un insieme di dati per distribuire più righe di una singola colonna in un'unica riga con più colonne . Questo in sostanza il risultato perni fissati lateralmente . Dati di esempio

Per capire meglio una tabella pivot , un esempio di alcuni dati di vendita sono elencati qui . Copiare il seguente in Microsoft SQL Server Management Studio per provare gli esempi .

Crea una tabella # PivotTestTable ( CustName varchar ( 8 ) , ITEM_TYPE varchar ( 8 ) , Item_Amount numerico (6,2 ) ) per

insert into # PivotTestTableselect ' Jason ', ' Computer' , 435.34unionselect ' Jason ', ' Software' , 243.54unionselect ' Jason ', ' Monitor' , 158.23unionselect ' Alison ', ' Computer' , 345.89unionselect ' Alison ' , 'Software' , 78.78unionselect ' Alison ', ' Monitor' , 123.45
partire dati UnPivoted

Quando la tabella temporanea , # PivotTestTable , viene eseguita una query , il risultato è il seguente .

CustName ITEM_TYPE Item_Amount -------- -------------------- Alison Computer 345.89Alison Monitor 123.45Alison Software 78.78Jason Computer 435.34Jason Monitor 158.23Jason Software 243,54

Come si può vedere , il set di risultati mostra due clienti , Alison e Jason , che hanno acquistato tre diversi tipi di oggetti . Vi sono sei file di dati per due clienti . Se volessimo vedere i dati in una singola riga per cliente , vorremmo utilizzare una tabella pivot per ottenere il risultato desiderato .
Pivot per funzione PIVOT

Microsoft SQL Server ha una funzione PIVOT integrato in SQL Server . Ecco un esempio con i dati # PivotTestTable .

SELECTCustName come Total_Sales_By_Cust , computer, monitor , SoftwareFROM ( SELECTCustName , ITEM_TYPE , Item_AmountFROM # PivotTestTable ) aPIVOT (Somma ( Item_Amount ) PER ITEM_TYPE in ( computer, monitor , software ) ) b

Questa query restituirà gli originali sei file imperniata in due righe con le colonne separate per ogni tipo di articolo venduto . Il set di risultati generato da questa query è qui :

Total_Sales_By_Cust Computer Software Monitor ------------------- ----------- --------------- Alison 345.89 123.45 435.34 158.23 243.54 78.78Jason
Pivot da aggregato caso Dichiarazione

utilizzando un aggregato funzione ( sUM , AVG , MIN , MAX) intorno a una istruzione case in una query SQL , siamo in grado di raggiungere lo stesso risultato della funzione PIVOT con meno lavoro .

SELECTCustName come Total_Sales_By_Cust , somma ( caso ITEM_TYPE quando ' Computer' poi fine Item_Amount ) come Computer , somma ( caso ITEM_TYPE quando poi fine Item_Amount 'Monitor' ) come monitor , somma ( caso ITEM_TYPE quando ' Software' poi fine Item_Amount ) come SoftwareFROM # PivotTestTableGROUP dA CustName

query restituirà lo stesso set di risultati dell'esempio precedente ed è solo una preferenza per il tipo di perno da usare.
errore comune con tabelle pivot

comune errore di creare una tabella pivot è quello di creare un join indietro sulla tabella di origine . Questo produrrà risultati non affidabili e deve essere evitato . Questo esempio è strettamente un esempio di cosa non fare . Il risultato in questo esempio sarà la stessa , ma questo esempio non funziona in tutti i casi

SELECTp1.CustName , p1.Item_Amount come Computer , p2.Item_Amount come Monitor , p3.Item_Amount come SoftwareFROM p1INNER # PivotTestTable . JOIN # PivotTestTable p2on p1.CustName = p2.CustNameand p2.Item_Type = ' Monitor'INNER join # PivotTestTable p3on p1.CustName = p3.CustNameand p3.Item_Type =' Software'WHERE p1.Item_Type = ' Computer'