Вопрос по sql, sql-server – SQL Server динамический запрос PIVOT?

167

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

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 Server, пожалуйста? Aaron Bertrand
возможный дубликатWrite advanced SQL Select RichardTheKiwi
спасибо за этот простой и полезный вопрос smoothumut

Ваш Ответ

7   ответов
212

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
Динамический динамический SQL PIVOT:gist.github.com/smoothdeveloper/6898062 :)
Неважно, я поставил заказ в неправильном месте.
Как бы вы отсортировали строки и столбцы на этом?
Так что \ @cols должен быть конкатенацией строк, верно? Мы не можем использовать sp_executesql и привязку параметров для интерполяции \ @cols там? Даже если мы сами создаем \ @cols, что если он каким-то образом содержит вредоносный SQL. Какие-нибудь дополнительные меры по смягчению, которые я мог бы предпринять, прежде чем объединять и выполнять его?
@PatrickSchomburg Есть множество способов - если вы хотите отсортировать@cols тогда вы могли бы удалитьDISTINCT и использоватьGROUP BY а такжеORDER BY когда вы получите список@cols.
18
Dynamic 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
6

льзовать QUOTENAME, чтобы избежать атак с использованием SQL-инъекций):

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

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

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

Я проголосовал за выбранный ответ (до того, как он был выбран), потому что у него был пример, и он лучше поможет кому-то новому. Однако я думаю, что кто-то новый должен также прочитать предоставленные мной ссылки, поэтому я не удалил их.
@ davids Пожалуйста, обратитесь кthis meta discussion, Если вы удалите гиперссылки, ваш ответ будет неполным.
@ Кермит, я согласен, что показ кода более полезен, но вы говорите, что он необходим для того, чтобы он был ответом? Без ссылок мой ответ будет "Вы можете добиться этого с помощью динамического TSQL". Выбранный ответ предлагает тот же маршрут, с дополнительным преимуществом, если также показывает, как это сделать, именно поэтому он был выбран в качестве ответа.
FWIWQUOTENAME Помогает атака SQL-инъекцией только в том случае, если вы принимаете @tableName в качестве параметра от пользователя и добавляете его в запрос, напримерSET @sql = 'SELECT * FROM ' + @tableName;, Вы можете создать множество уязвимых динамических строк SQL иQUOTENAME не сделаю лизать, чтобы помочь вам.
4

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

использующая функцию STRING_AGG для построения списка сводных столбцов:

create table temp
(
    date datetime,
    category varchar(3),
    amount money
);

insert into temp values ('20120101', 'ABC', 1000.00);
insert into temp values ('20120201', 'DEF', 500.00);
insert into temp values ('20120201', 'GHI', 800.00);
insert into temp values ('20120210', 'DEF', 700.00);
insert into temp values ('20120301', 'ABC', 1100.00);


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = (SELECT STRING_AGG(category,',') FROM (SELECT DISTINCT category FROM temp WHERE category IS NOT NULL)t);

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

NULL вzero на выходе.

Table creation and data insertion:

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

OUTPUT :

enter image description here

9

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

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

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

Эта процедура будет использовать ключевые переменные оператора сводки для динамического создания операторов сводки для переменных таблиц, имен столбцов и агрегатов. Столбец Static используется в качестве столбца group by / identity для сводной таблицы (его можно удалить из кода, если в этом нет необходимости, но он довольно распространен в операторах сводной таблицы и был необходим для решения исходной проблемы), где в столбце сводной таблицы конечные результирующие имена столбцов будут сгенерированы, а столбец значений - это то, к чему будет применяться агрегат. Параметр 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 '[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

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

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'

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

enter image description here

К сожалению, нет, насколько мне известно, потому что у вас не может быть динамической структуры для TVF. Вы должны иметь статический набор столбцов в TVF.
Хорошая работа! Можете ли вы сделать опцию TVF вместо хранимой процедуры. Было бы удобно выбрать из такого TVF.

Похожие вопросы