Динамический запрос PIVOT SQL Server?

Мне поручено придумать способ перевода следующих данных:

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

в следующем:

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

Пустое пятна могут быть NULL или пробелами, либо все в порядке, и категории должны быть динамическими. Еще одно возможное предостережение заключается в том, что мы будем запускать запрос в ограниченной емкости, что означает, что временные таблицы отсутствуют. Я пытался исследовать и приземлился, PIVOTно, поскольку я никогда не использовал это, прежде чем я действительно не понимаю этого, несмотря на все мои усилия, чтобы понять это. Может кто-то указать мне верное направление?

sql,sql-server,tsql,pivot,

146

Ответов: 6


191 принят

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

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

Результаты:

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

13

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

Разный подход для создания строки столбца

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

Я знаю, что этот вопрос старше, но я искал ответы и думал, что я могу расширить «динамическую» часть проблемы и, возможно, помочь кому-то.

В первую очередь я построил это решение для решения проблемы, с которой несколько сотрудников столкнулись с непостоянными и большими наборами данных, которые нужно быстро поворачивать.

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

Эта процедура будет принимать ключевые переменные сводной инструкции для динамического создания сводных операторов для разных таблиц, имен столбцов и агрегатов. Столбец Static используется как столбец / идентификатор группы для сводной таблицы (это может быть удалено из кода, если это не необходимо, но довольно часто используется в сводных операторах и было необходимо для решения исходной проблемы), столбец столбца - это то, где конечные имена столбцов будут генерироваться, а столбец значений - это то, к чему будет применяться совокупность. Параметр Table - это имя таблицы, включая схему (schema.tablename), эта часть кода может использовать некоторую любовь, потому что она не такая чистая, как мне бы хотелось. Это работало для меня, потому что мое использование не было публично, и SQL-инъекция не вызывала беспокойства. Параметр Aggregate примет любой стандартный sql-агрегат «AVG», «SUM», «MAX» и т. Д. Код также по умолчанию имеет значение 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)

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

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'

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

введите описание изображения здесь


6

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

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

SQL Server - Динамическая таблица PIVOT - SQL-инъекция

Обязательная ссылка на Curse and Blessings of Dynamic SQL


3

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

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);
sql, sql-server, tsql, pivot,