Utilizzo di PIVOT in server SQL con esempio di SQL dinamico
Autore
Nell'ultimo articolo, ho scritto sull'utilizzo di PIVOT in uno scenario standard in cui conosci tutte le colonne che desideri, utilizzando gli stessi dati su cui ora possiamo basarci per produrre nomi di colonna dipendenti dal set di dati effettivo.
Salterò la prima parte di quell'articolo ma fornirò l'SQL per metterci in funzione qui. Innanzitutto, creiamo un set di dati.
CREATE TYPE c1bs_Piv AS TABLE(MyRow NVARCHAR(10),--Row IDMyCol NVARCHAR(10),--Value to become columnCalcCol INT--Value to be Summed)GODECLARE @Piv c1bs_PivINSERT INTO @PivSELECT '1','1',1 UNIONSELECT '1','s',1 UNIONSELECT '1','3',1 UNIONSELECT '2','2',6 UNIONSELECT '2','2',1 UNIONSELECT '2','3',1 UNIONSELECT '3','5',5 UNIONSELECT '3','2',1 UNIONSELECT '3','3',1 UNIONSELECT '3','3',3È stato utile?
Nell'altro articolo, abbiamo codificato a fondo i nomi delle colonne (1,2,3), tuttavia, questo potrebbe non essere sempre fattibile, a volte i set di record potrebbero dover crescere quando, ad esempio, si invia un set di dati a una GUI, c'è un aggirandolo, esaminiamolo passo dopo passo;
- Crea un tipo di tabella per rappresentare i tuoi dati come sopra.
- Dichiara una tabella e inserisci i tuoi dati in essa (@Piv)
- Dichiara due variabili, una per mantenere i nomi delle colonne su Pivot e una per l'elenco SELECT, in questo esempio, usando un ISNULL e i nomi delle colonne eliminano qualsiasi valore NULL. Abbiamo anche usato COALESCE per concatenare i nomi in una stringa.
- Dichiara un'altra variabile, con la tabella @Piv, questi sono parametri che vengono passati in seguito.
- Dichiara il nostro SQL, è qui che inseriamo i nomi delle colonne e selezioniamo l'elenco in una versione modificata del semplice PIVOT.
- ESEGUIRE l'istruzione SQL sopra e passare i parametri e la tabella @PIV.
--Complex Pivot - unknown column names--Get Column Names for belowDECLARE @Cols NVARCHAR(MAX),@Sel NVARCHAR(MAX) SELECT @Cols=COALESCE(@Cols+',','')+'['+MyCol+']',--Concatenate the Columns @Sel =COALESCE(@Sel +',','')+'ISNULL(['+MyCol+'], 0) AS ['+MyCol+']' --Concatenate the Columns into a select listFROM @Piv GROUP BY MyCol
--Columns list displaySELECT @Cols ColumnList,@Sel SelectList
--Complex Pivot SQLDECLARE @Params NVARCHAR(MAX)='@Piv c1bs_Piv READONLY'DECLARE @SQL NVARCHAR(MAX)='SELECT [MyRow],'+@Sel+'FROM @PivPIVOT (SUM(CalcCol)FOR MyColIN ('+@Cols+')) AS MyTable'
--Execute SQLEXECUTE sp_executesql @SQL,--SQL String from above@Params,--Parameter list@Piv--Temp Table needs to be passed in, can only be read only?È stato utile?
Il nostro set di dati è ora cresciuto per includere due colonne aggiuntive dal set di dati (5,s)
| MyRow | 1 | 2 | 3 | 5 | s |
| 1 | 1 | 0 | 1 | 0 | 1 |
| 2 | 0 | 7 | 1 | 0 | 0 |
| 3 | 0 | 1 | 4 | 5 | 0 |
