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.