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

Question

01 февр. 2018 г., 12:20

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

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

<code>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
</code>

в следующее:

<code>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
</code>

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

Aaron Bertrand01 мая 2012 г., 23:14

Какую версию SQL Server, пожалуйста?

RichardTheKiwi03 мая 2013 г., 11:43

возможный дубликатWrite advanced SQL Select

smoothumut23 дек. 2017 г., 09:25

спасибо за этот простой и полезный вопрос

Ответы на вопрос(7)

21 июл. 2016 г., 15:18

mkdave99

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

21 дек. 2015 г., 16:09

m0rg4n

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

27 февр. 2017 г., 16:29

Arockia Nirmal

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 :

23 мая 2017 г., 14:34

Communitydavids

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

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

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

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

29 апр. 2014 г., 20:06

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

01 апр. 2014 г., 18:17

@ davids Пожалуйста, обратитесь кthis meta discussion, Если вы удалите гиперссылки, ваш ответ будет неполным.

29 апр. 2014 г., 20:04

@ Кермит, я согласен, что показ кода более полезен, но вы говорите, что он необходим для того, чтобы он был ответом? Без ссылок мой ответ будет "Вы можете добиться этого с помощью динамического TSQL". Выбранный ответ предлагает тот же маршрут, с дополнительным преимуществом, если также показывает, как это сделать, именно поэтому он был выбран в качестве ответа.

01 мая 2012 г., 23:13

FWIWQUOTENAME Помогает атака SQL-инъекцией только в том случае, если вы принимаете @tableName в качестве параметра от пользователя и добавляете его в запрос, напримерSET @sql = 'SELECT * FROM ' + @tableName;, Вы можете создать множество уязвимых динамических строк SQL иQUOTENAME не сделаю лизать, чтобы помочь вам.

24 янв. 2019 г., 17:22

nvogel

использующая функцию 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;

12 июл. 2017 г., 20:58

SFrejofsky

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

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

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

Эта процедура будет использовать ключевые переменные оператора сводки для динамического создания операторов сводки для переменных таблиц, имен столбцов и агрегатов. Столбец 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'

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

28 сент. 2017 г., 21:36

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

28 сент. 2017 г., 13:21

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

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

Ответы на вопрос(7)

Ваш ответ на вопрос

Популярные вопросы

Вы очень активны! Это здорово!

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

Ответы на вопрос(7)

Ваш ответ на вопрос

Популярные вопросы