Beispiel für SQL Server mit PIVOT und dynamischem SQL
Im letzten Artikel habe ich über die Verwendung von PIVOT in einem Standardszenario geschrieben, in dem Sie alle gewünschten Spalten kennen und dieselben Daten verwenden, auf denen wir nun aufbauen können, um Spaltennamen zu erstellen, die vom tatsächlichen Datensatz abhängen.
Ich werde den ersten Teil dieses Artikels überspringen, aber das SQL bereitstellen, um uns hier zum Laufen zu bringen. Lassen Sie uns zunächst einen Datensatz erstellen.
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',3In dem anderen Artikel haben wir die Spaltennamen fest codiert (1,2,3), dies ist jedoch möglicherweise nicht immer praktikabel, manchmal müssen die Datensätze möglicherweise wachsen, wenn beispielsweise ein Datensatz an eine GUI gesendet wird Gehen wir es Schritt für Schritt durch;
- Erstellen Sie einen Tabellentyp, um Ihre Daten wie oben darzustellen.
- Deklarieren Sie eine Tabelle und fügen Sie Ihre Daten darin ein (@Piv)
- Deklarieren Sie zwei Variablen, eine zum Speichern der Spaltennamen für Pivot und eine für die SELECT-Liste, in diesem Beispiel, indem Sie eine ISNULL verwenden und die Spaltennamen alle NULL-Werte entfernen. Wir haben auch COALESCE verwendet, um die Namen zu einer Zeichenfolge zu verketten.
- Deklarieren Sie eine weitere Variable, mit der @Piv-Tabelle, das sind Parameter, die später weitergegeben werden.
- Deklarieren Sie unser SQL, hier fügen wir die Spaltennamen und die Auswahlliste in eine modifizierte Version des einfachen PIVOT ein.
- Führen Sie die obige SQL-Anweisung AUS und übergeben Sie die Parameter und die @PIV-Tabelle.
--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?Unser Datensatz ist jetzt um zwei zusätzliche Spalten aus dem Datensatz (5,s) angewachsen.
| MyRow | 1 | 2 | 3 | 5 | s |
| 1 | 1 | 0 | 1 | 0 | 1 |
| 2 | 0 | 7 | 1 | 0 | 0 |
| 3 | 0 | 1 | 4 | 5 | 0 |
