dinamico - columnas dinamicas sql server

¿Consultas PIVOT dinámicas de SQL Server?

Question

Me han encomendado la tarea de encontrar los medios para traducir los siguientes datos:

date category amount
1/1/2012 ABC 1000.00
2/1/2012 DEF 500.00
2/1/2012 GHI 800.00
2/10/2012 DEF 700.00
3/1/2012 ABC 1100.00

en lo siguiente:

date ABC DEF GHI
1/1/2012 1000.00
2/1/2012 500.00
2/1/2012 800.00
2/10/2012 700.00
3/1/2012 1100.00

Los espacios en blanco pueden ser NULL o espacios en blanco, o bien, y las categorías deberían ser dinámicas. Otra posible advertencia a esto es que ejecutaremos la consulta en una capacidad limitada, lo que significa que las tablas temporales están desactivadas. Intenté investigar y aterrizar en PIVOT pero como nunca lo había usado antes, realmente no lo entiendo, a pesar de mis mejores esfuerzos para resolverlo. ¿Alguien puede señalarme en la dirección correcta?

Answer 1

Dynamic SQL PIVOT

Un 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

Answer 2

Dynamic PIVOT SQL:

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

Answer 3

El siguiente código proporciona los resultados que reemplazan NULL a cero en la salida.

Creación de tabla e inserción de datos:

create table test_table
(
date nvarchar(10),
category char(3),
amount money
)
insert into test_table values (''1/1/2012'',''ABC'',1000.00)
insert into test_table values (''2/1/2012'',''DEF'',500.00)
insert into test_table values (''2/1/2012'',''GHI'',800.00)
insert into test_table values (''2/10/2012'',''DEF'',700.00)
insert into test_table values (''3/1/2012'',''ABC'',1100.00)

Consulta para generar los resultados exactos que también reemplaza NULL con ceros:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
@PivotColumnNames AS NVARCHAR(MAX),
@PivotSelectColumnNames AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + '','','''')
+ QUOTENAME(category)
FROM (SELECT DISTINCT category FROM test_table) AS cat
--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames
= ISNULL(@PivotSelectColumnNames + '','','''')
+ ''ISNULL('' + QUOTENAME(category) + '', 0) AS ''
+ QUOTENAME(category)
FROM (SELECT DISTINCT category FROM test_table) AS cat
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N''SELECT date, '' + @PivotSelectColumnNames + ''
FROM test_table
pivot(sum(amount) for category in ('' + @PivotColumnNames + '')) as pvt'';
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

SALIDA:

Answer 4

Hay mi solución limpiando los valores nulos innecesarios

DECLARE @cols AS NVARCHAR(MAX),
@maxcols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT '','' + QUOTENAME(CodigoFormaPago)
from PO_FormasPago
order by CodigoFormaPago
FOR XML PATH(''''), TYPE
).value(''.'', ''NVARCHAR(MAX)'')
,1,1,'''')
select @maxcols = STUFF((SELECT '',MAX('' + QUOTENAME(CodigoFormaPago) + '') as '' + QUOTENAME(CodigoFormaPago)
from PO_FormasPago
order by CodigoFormaPago
FOR XML PATH(''''), TYPE
).value(''.'', ''NVARCHAR(MAX)'')
,1,1,'''')
set @query = ''SELECT CodigoProducto, DenominacionProducto, '' + @maxcols + ''
FROM
(
SELECT
CodigoProducto, DenominacionProducto,
'' + @cols + '' from
(
SELECT
p.CodigoProducto as CodigoProducto,
p.DenominacionProducto as DenominacionProducto,
fpp.CantidadCuotas as CantidadCuotas,
fpp.IdFormaPago as IdFormaPago,
fp.CodigoFormaPago as CodigoFormaPago
FROM
PR_Producto p
LEFT JOIN PR_FormasPagoProducto fpp
ON fpp.IdProducto = p.IdProducto
LEFT JOIN PO_FormasPago fp
ON fpp.IdFormaPago = fp.IdFormaPago
) xp
pivot
(
MAX(CantidadCuotas)
for CodigoFormaPago in ('' + @cols + '')
) p
) xx
GROUP BY CodigoProducto, DenominacionProducto''
t @query;
execute(@query);

Answer 5

Puede lograr esto usando TSQL dinámico (recuerde usar QUOTENAME para evitar ataques de inyección SQL):

Pivots con columnas dinámicas en SQL Server 2005

SQL Server - Tabla Dynamic PIVOT - Inyección SQL

Referencia obligatoria a The Curse and Blessings of Dynamic SQL

Answer 6

Sé que esta pregunta es más antigua, pero estaba buscando las respuestas y pensé que podría ampliar la parte "dinámica" del problema y posiblemente ayudar a alguien.

En primer lugar, construí esta solución para resolver un problema que un par de compañeros de trabajo tenían con conjuntos de datos inconstantes y grandes que debían pivotarse rápidamente.

Esta solución requiere la creación de un procedimiento almacenado, por lo tanto, si eso está fuera de lugar para sus necesidades, por favor, deje de leer ahora.

Este procedimiento tendrá en cuenta las variables clave de una declaración dinámica para crear dinámicamente declaraciones dinámicas para diferentes tablas, nombres de columnas y agregados. La columna estática se utiliza como el grupo por / columna de identidad para el pivote (puede eliminarse del código si no es necesario, pero es bastante común en las declaraciones pivote y fue necesario para resolver el problema original), la columna pivote es donde el se generarán los nombres de columna resultantes y la columna de valor es a qué se aplicará el agregado. El parámetro Table es el nombre de la tabla que incluye el esquema (schema.tablename). Esta porción del código podría usar algo de amor porque no es tan limpia como me gustaría que fuera. Me funcionó porque mi uso no era público y la inyección de sql no era una preocupación. El parámetro Aggregate aceptará cualquier agregado SQL estándar ''AVG'', ''SUM'', ''MAX'', etc. El código también se establece en MAX como agregado, esto no es necesario, pero la audiencia para la que se construyó originalmente no entendía los pivots y eran típicamente usando max como un 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 posteriores, 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 ''+@TABLE+''
WHERE ISNULL(''+@PIVOT_COLUMN+'','''''''') <> ''''''''
FOR XML PATH(''''''''), TYPE)
.value(''''.'''',''''NVARCHAR(MAX)''''),1,2,'''' '''') as PIVOT_VALUES
from ''+@TABLE+'' 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 (''+@STATIC_COLUMN+'' NVARCHAR(255) NULL,''+@TEMPVARCOLUMNS+'')
INSERT INTO @RETURN_TABLE(''+@STATIC_COLUMN+'',''+@AVAIABLE_TO_PIVOT+'')
select * from (
SELECT '' + @STATIC_COLUMN + '' , '' + @PIVOT_COLUMN + '', '' + @VALUE_COLUMN + '' FROM ''+@TABLE+'' ) a
PIVOT
(
''+@AGGREGATE+''(''+@VALUE_COLUMN+'')
FOR ''+@PIVOT_COLUMN+'' IN (''+@AVAIABLE_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 diversas sentencias de ejecución que muestran los agregados variados como un ejemplo simple. No opté por cambiar las columnas estática, pivote y valor para mantener el ejemplo simple. Debería poder copiar y pegar el código para comenzar a jugarlo 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.