Динамический SQL-сервер сводных запросов?

Динамический SQL PIVOT

Другой подход к созданию столбцов string

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

Результат

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 для динамического создания операторов pivot для различных таблиц, имен столбцов и агрегатов. Статический столбец используется в качестве столбца group by / identity для pivot (он может быть удален из кода, если это не требуется, но довольно часто встречается в операторах pivot и был необходим для решения исходной проблемы), столбец pivot-это место, где будут сгенерированы конечные результирующие имена столбцов, а столбец value это то, к чему будет применяться агрегат. Параметр Table - это имя таблицы, включающей схему (schema.tablename) эта часть кода может использовать некоторую любовь, потому что она не так чиста, как мне бы хотелось. Это сработало для меня, потому что мое использование не было открыто, и инъекция sql не была проблемой. Совокупный параметр может принять любой стандартный SQL агрегатные 'авг', 'сумма', 'Макс' и т. д. Код также по умолчанию принимает значение MAX в качестве агрегата это не обязательно но аудитория это изначально был построен для не понимающих пивотов и, как правило, использовал max в качестве агрегата.

Давайте начнем с кода для создания хранимой процедуры. Этот код должен работать во всех версиях SSMS 2005 и выше, но я не тестировал его в 2005 или 2016 годах, но я не вижу, почему он не будет работать.

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

Далее мы подготовим наши данные для примера. Я взял пример данных из принятого ответа с добавлением нескольких элементов данных для использования в этом доказательстве концепции, чтобы показать различные результаты совокупного изменения.

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)

Следующие примеры показывают различные операторы выполнения, показывающие различные агрегаты в качестве простого примера. Я не стал менять столбцы static, pivot и value, чтобы упростить пример. Вы должны быть в состоянии просто скопировать и вставить код, чтобы начать возиться с ним самостоятельно

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'

Это выполнение возвращает следующие наборы данных соответственно.

Этого можно достичь с помощью dynamic TSQL (не забудьте использовать QUOTENAME, чтобы избежать атак SQL-инъекций):

Пивоты с динамическими столбцами в SQL Server 2005

SQL Server-динамическая сводная таблица-SQL Injection

Обязательная ссылка на проклятие и благословение динамического SQL

Вот мое решение, очищающее ненужные нулевые значения

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);

Приведенный ниже код предоставляет результаты, которые заменяютNULL наноль в выходных данных.

Создание таблицы и вставка данных:

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)

Запрос для получения точных результатов, который также заменяет NULL на нули:

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

Вывод :