Eu tenho uma consulta SQL Server que obtém os dados de vendas para uma cadeia de mercado de 44 lojas, inclui 4 valores para cada loja (Vendas, CPV, GP e GP Margin) , declarei um CTE e entrei nele 44 vezes para cada loja obter os 4 valores como colunas para cada loja conforme mostrado abaixo:
Consulta: Consulta1
mas quando tento usar PIVOTfunção com SQL dinâmico ele retorna vários nulos conforme mostrado abaixo:
Consulta: Consulta2
table ##tbl1inclui o conjunto de dados que quero dinamizar:
Usei a seguinte consulta: (os três pontos representam o restante das colunas a serem dinamizadas)
select * from ##tbl1
pivot (sum(total_sales) for s in ([50001 Sales],[50002 Sales],...)) as pv_tb
pivot (sum(Margin) for m in ([50001 margin],[50002 margin],...)) as pv_tb1
pivot (sum(total_profit) for p in ([50001 profit],[50002 profit],...)) as pv_tb2
pivot (sum(total_cost) for c in ([50001 cost],[50002 cost],...)) as pv_tb3
Eu também uso SQL dinâmico para passar os nomes das colunas para dentro PIVOT()sem ter que escrever cada uma delas individualmente, mas por uma questão de solução de problemas eu apenas mostrei como a consulta ficaria.
Não consigo usar CROSS APPLYe UNPIVOTcom esse resultado de 132 colunas, é muito difícil manter.
Alguém pode me ajudar a encontrar uma maneira mais fácil do que isso?
O resultado é
Aqui está a minha solução dinâmica para esta questão. Acho que o problema que você está tendo com a instrução pivô é que todas as colunas estão presentes quando você gira, o que fornece esses NULLs errôneos. Mesmo que eles não estejam na lista de seleção, o operador de pivô agrupa por eles (veja como eu faço uma sub-seleção na seção de pivô).
Quanto ao desempenho, sob o capô, a diferença de desempenho é insignificante, PIVOT e UNPIVOT são açúcar sintático sobre SQL aparentemente feio/complicado. Eu prefiro usá-los, pois acho que mantém o código mais fácil de ler com menos ruído.
De qualquer forma, esta solução dá os resultados desejados e o faz dinamicamente. A única coisa que eu tive que mudar foram os tipos de dados. O operador UNPIVOT requer que todos sejam do mesmo tipo de dados, então eu apenas os forcei a serem NUMERIC(38,6), você pode ter que adicionar uma etapa de conversão/CTE à mistura.
Uma coisa a notar é que ele não faz nenhuma filtragem da tabela de origem. Se você precisar adicionar filtragem, precisará atualizá-la em vários locais ou a consulta começará a fornecer esses resultados com os valores NULL. Melhor despejar os resultados desejados em uma tabela temporária nesse caso.
Finalmente: Este é realmente um trabalho mais adequado para a camada de exibição. Não tenho certeza para onde os resultados estão indo, mas o SSRS e o Excel têm excelentes operações de pivô incorporadas e farão um trabalho melhor ao lidar com isso dinamicamente do que você pode fazer no SQL. Mesmo assim, minha solução: