SQL Server usando PIVOT con ejemplo de SQL dinámico
En el último artículo, escribí sobre el uso de PIVOT en un escenario estándar en el que conoce todas las columnas que desea, usando los mismos datos que ahora podemos construir para producir nombres de columna que dependen del conjunto de datos real.
Omitiré la primera parte de ese artículo, pero proporcionaré el SQL para que podamos comenzar a trabajar aquí. Primero, vamos a crear un conjunto de datos.
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',3En el otro artículo, codificamos los nombres de las columnas (1,2,3), sin embargo, esto puede no ser siempre viable, a veces los conjuntos de registros pueden necesitar crecer cuando, por ejemplo, al enviar un conjunto de datos a una GUI, hay un rodeándolo, repasémoslo paso a paso;
- Cree un tipo de tabla para representar sus datos como se indica arriba.
- Declare una tabla e inserte sus datos en ella (@Piv)
- Declare dos variables, una para mantener los nombres de las columnas en Pivot y otra para la lista SELECT, en este ejemplo, usando un ISNULL y los nombres de las columnas se deshacen de cualquier valor NULL. También hemos usado COALESCE para concatenar los nombres en una cadena.
- Declare otra variable, con la tabla @Piv, estos son parámetros que se pasan más tarde.
- Declare nuestro SQL, aquí es donde inyectamos los nombres de las columnas y seleccionamos la lista en una versión modificada del PIVOT simple.
- EJECUTAR la instrucción SQL anterior y pasar los parámetros y la tabla @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?Nuestro conjunto de datos ahora ha crecido para incluir dos columnas adicionales del conjunto de datos (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 |
