Solución:
PIVOT de SQL dinámico:
create table temp
(
date datetime,
category varchar(3),
amount money
)
insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('3/1/2012', 'ABC', 1100.00)
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category)
FROM temp c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT date, ' + @cols + ' from
(
select date
, amount
, category
from temp
) x
pivot
(
max(amount)
for category in (' + @cols + ')
) p '
execute(@query)
drop table temp
Resultados:
Date ABC DEF GHI
2012-01-01 00:00:00.000 1000.00 NULL NULL
2012-02-01 00:00:00.000 NULL 500.00 800.00
2012-02-10 00:00:00.000 NULL 700.00 NULL
2012-03-01 00:00:00.000 1100.00 NULL NULL
PIVOT de SQL dinámico
Enfoque diferente para crear cadenas de columnas
create table #temp
(
date datetime,
category varchar(3),
amount money
)
insert into #temp values ('1/1/2012', 'ABC', 1000.00)
insert into #temp values ('2/1/2012', 'DEF', 500.00)
insert into #temp values ('2/1/2012', 'GHI', 800.00)
insert into #temp values ('2/10/2012', 'DEF', 700.00)
insert into #temp values ('3/1/2012', 'ABC', 1100.00)
DECLARE @cols AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';
SELECT @cols = @cols + QUOTENAME(category) + ',' FROM (select distinct category from #temp ) as tmp
select @cols = substring(@cols, 0, len(@cols)) --trim "," at end
set @query =
'SELECT * from
(
select date, amount, category from #temp
) src
pivot
(
max(amount) for category in (' + @cols + ')
) piv'
execute(@query)
drop table #temp
Resultado
date ABC DEF GHI
2012-01-01 00:00:00.000 1000.00 NULL NULL
2012-02-01 00:00:00.000 NULL 500.00 800.00
2012-02-10 00:00:00.000 NULL 700.00 NULL
2012-03-01 00:00:00.000 1100.00 NULL NULL
Sé que esta pregunta es más antigua, pero estaba mirando las respuestas y pensé que podría ampliar la parte “dinámica” del problema y posiblemente ayudar a alguien.
En primer lugar, desarrollé esta solución para resolver un problema que tenían un par de compañeros de trabajo con conjuntos de datos grandes e inconstantes que debían pivotarse rápidamente.
Esta solución requiere la creación de un procedimiento almacenado, por lo que si eso está fuera de discusión para sus necesidades, deje de leer ahora.
Este procedimiento tomará las variables clave de una declaración de pivote para crear dinámicamente declaraciones de pivote para diferentes tablas, nombres de columnas y agregados. La columna Estática se usa como la columna de grupo por / identidad para el pivote (esto se puede eliminar del código si no es necesario, pero es bastante común en las declaraciones dinámicas y era necesario para resolver el problema original), la columna pivote es donde el end se generarán los nombres de las columnas resultantes, y la columna de valor es a lo que se aplicará el agregado. El parámetro Table es el nombre de la tabla, incluido el esquema (schema.tablename), esta parte del código podría necesitar algo de amor porque no está tan limpio como me gustaría. Funcionó para mí porque mi uso no era público y la inyección de sql no era una preocupación. El parámetro Agregado aceptará cualquier agregado SQL estándar ‘AVG’, ‘SUM’, ‘MAX’, etc. El código también predeterminado es MAX como agregado, esto no es necesario, pero la audiencia para la que se creó originalmente no entendía los pivotes y normalmente usando max como agregado.
Comencemos con el código para crear el procedimiento almacenado. Este código debería funcionar en todas las versiones de SSMS 2005 y superiores, pero no lo he probado en 2005 o 2016, pero no veo por qué no funcionaría.
create PROCEDURE [dbo].[USP_DYNAMIC_PIVOT]
(
@STATIC_COLUMN VARCHAR(255),
@PIVOT_COLUMN VARCHAR(255),
@VALUE_COLUMN VARCHAR(255),
@TABLE VARCHAR(255),
@AGGREGATE VARCHAR(20) = null
)
AS
BEGIN
SET NOCOUNT ON;
declare @AVAIABLE_TO_PIVOT NVARCHAR(MAX),
@SQLSTRING NVARCHAR(MAX),
@PIVOT_SQL_STRING NVARCHAR(MAX),
@TEMPVARCOLUMNS NVARCHAR(MAX),
@TABLESQL NVARCHAR(MAX)
if isnull(@AGGREGATE,'') = ''
begin
SET @AGGREGATE = 'MAX'
end
SET @PIVOT_SQL_STRING = 'SELECT top 1 STUFF((SELECT distinct '', '' + CAST(''[''+CONVERT(VARCHAR,'+ @PIVOT_COLUMN+')+'']'' AS VARCHAR(50)) [text()]
FROM '[email protected]+'
WHERE ISNULL('[email protected]_COLUMN+','''') <> ''''
FOR XML PATH(''''), TYPE)
.value(''.'',''NVARCHAR(MAX)''),1,2,'' '') as PIVOT_VALUES
from '[email protected]+' ma
ORDER BY ' + @PIVOT_COLUMN + ''
declare @TAB AS TABLE(COL NVARCHAR(MAX) )
INSERT INTO @TAB EXEC SP_EXECUTESQL @PIVOT_SQL_STRING, @AVAIABLE_TO_PIVOT
SET @AVAIABLE_TO_PIVOT = (SELECT * FROM @TAB)
SET @TEMPVARCOLUMNS = (SELECT replace(@AVAIABLE_TO_PIVOT,',',' nvarchar(255) null,') + ' nvarchar(255) null')
SET @SQLSTRING = 'DECLARE @RETURN_TABLE TABLE ('[email protected]_COLUMN+' NVARCHAR(255) NULL,'[email protected]+')
INSERT INTO @RETURN_TABLE('[email protected]_COLUMN+','[email protected]_TO_PIVOT+')
select * from (
SELECT ' + @STATIC_COLUMN + ' , ' + @PIVOT_COLUMN + ', ' + @VALUE_COLUMN + ' FROM '[email protected]+' ) a
PIVOT
(
'[email protected]+'('[email protected]_COLUMN+')
FOR '[email protected]_COLUMN+' IN ('[email protected]_TO_PIVOT+')
) piv
SELECT * FROM @RETURN_TABLE'
EXEC SP_EXECUTESQL @SQLSTRING
END
A continuación, prepararemos nuestros datos para el ejemplo. He tomado el ejemplo de datos de la respuesta aceptada con la adición de un par de elementos de datos para usar en esta prueba de concepto para mostrar los resultados variados del cambio agregado.
create table temp
(
date datetime,
category varchar(3),
amount money
)
insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('1/1/2012', 'ABC', 2000.00) -- added
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'DEF', 1500.00) -- added
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('2/10/2012', 'DEF', 800.00) -- addded
insert into temp values ('3/1/2012', 'ABC', 1100.00)
Los siguientes ejemplos muestran las declaraciones de ejecución variadas que muestran los agregados variados como un ejemplo simple. No opté por cambiar las columnas estática, dinámica y de valor para mantener el ejemplo simple. Debería poder simplemente copiar y pegar el código para comenzar a jugar con él usted mismo
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','sum'
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','max'
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','avg'
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','min'
Esta ejecución devuelve los siguientes conjuntos de datos respectivamente.
