Consulta PIVOT dinâmica do SQL Server?

Eu fui encarregado de encontrar um meio de traduzir os seguintes dados:

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 

no seguinte:

 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 

Os pontos em branco podem ser NULLs ou espaços em branco, bem, e as categorias precisariam ser dinâmicas. Outra possível advertência é que estaremos executando a consulta em uma capacidade limitada, o que significa que as tabelas temporárias estão fora. Eu tentei pesquisar e PIVOT no PIVOT mas como eu nunca usei isso antes, eu realmente não entendo, apesar dos meus melhores esforços para descobrir isso. Alguém pode me apontar na direção certa?

SQL dynamic 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 

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 

Dynamic Pivot SQL

Abordagem diferente para criar colunas de 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 

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 

Eu sei que esta pergunta é mais antiga, mas eu estava olhando através das respostas e pensei que eu poderia ser capaz de expandir a parte “dinâmica” do problema e, possivelmente, ajudar alguém para fora.

Primeiramente, eu construí esta solução para resolver um problema que alguns colegas de trabalho estavam tendo com conjuntos de dados inconstantes e grandes, que precisavam ser dinamizados rapidamente.

Esta solução requer a criação de um procedimento armazenado, portanto, se isso estiver fora de questão para as suas necessidades, por favor, pare de ler agora.

Esse procedimento vai considerar as variables-chave de uma instrução dinâmica para criar dinamicamente instruções dinâmicas para tabelas, nomes de coluna e agregações variables. A coluna Static é usada como o grupo pela coluna / identity para o pivot (isso pode ser retirado do código se não for necessário, mas é bastante comum em instruções dinâmicas e era necessário para resolver o problema original), a coluna dinâmica é onde o os nomes das colunas resultantes finais serão gerados a partir de e a coluna de valor é a que o agregado será aplicado. O parâmetro Table é o nome da tabela que inclui o esquema (schema.tablename). Essa parte do código pode usar algum tipo de amor porque não é tão limpa quanto eu gostaria que fosse. Funcionou para mim porque o meu uso não estava voltado para o público e a injeção de sql não era uma preocupação. O parâmetro Aggregate aceitará qualquer agregado sql padrão ‘AVG’, ‘SUM’, ‘MAX’ etc. O código também é padronizado como MAX como um agregado. Isso não é necessário, mas o público para o qual ele foi originalmente criado não entendia os pivôs e era típico usando max como um agregado.

Vamos começar com o código para criar o procedimento armazenado. Este código deve funcionar em todas as versões do SSMS 2005 e acima, mas eu não testei em 2005 ou 2016, mas não vejo por que não funcionaria.

 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 

Em seguida, obteremos nossos dados prontos para o exemplo. Tomei o exemplo de dados da resposta aceita com a adição de um par de elementos de dados para usar nesta prova de conceito para mostrar os resultados variados da mudança agregada.

 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) 

Os exemplos a seguir mostram as instruções de execução variadas mostrando os agregados variados como um exemplo simples. Eu não optei por alterar as colunas estática, dinâmica e de valor para manter o exemplo simples. Você deve poder apenas copiar e colar o código para começar a mexer com ele mesmo

 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 execução retorna os seguintes conjuntos de dados, respectivamente.

insira a descrição da imagem aqui

Você pode conseguir isso usando TSQL dynamic (lembre-se de usar o QUOTENAME para evitar ataques de injeção de SQL):

Pivots com colunas dinâmicas no SQL Server 2005

SQL Server – Tabela Dinâmica de Pivot – Injeção de SQL

Referência obrigatória a The Curse and Blessings do SQL dynamic

A minha solução está limpando os valores nulos desnecessários

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

O código abaixo fornece os resultados que substituem NULL para zero na saída.

Criação de tabelas e inserção de dados:

 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 gerar os resultados exatos que também substituem NULL por zeros:

 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 

SAÍDA:

insira a descrição da imagem aqui