Guia para criar consultas de tabela pivô padrão e dinâmica em SQL

Olá a todos,

Ao visualizar um relatório de consulta, há momentos em que se deseja transpor linhas em colunas. Uma maneira é copiar e colar para uma planilha e fazer a pivotação lá. Outra maneira é criar uma consulta de pivô em SQL.

Existem duas maneiras de criar uma consulta de pivô em SQL.

a) Consulta de tabela pivô padrão/estática

b) Consulta de tabela pivô dinâmica

a) Consulta de tabela pivô padrão/estática

Neste tipo, a lista de valores distintos em uma coluna a ser pivotada é fixa.

A sintaxe básica é

Select * from

(sua consulta aqui) t

Pivot

((coluna agregada) PARA (coluna pivoteada) EM ([valor 1], [valor 2],.. [valor n])) as pvt

Na consulta de tabela pivô padrão/estática acima, você deve listar os valores distintos das colunas a serem pivotadas após o operador IN.

Portanto, se um novo valor for adicionado à coluna a ser pivotada, mas não estiver codificado no operador IN da consulta de operador pivot, então esse valor não aparecerá no resultado pivotado. Devido a essa limitação, muitos preferem simplesmente executar a consulta normal e fazer a pivotação em uma planilha.

b) Consulta de tabela pivô dinâmica

Este tipo supera a limitação de colunas fixas de uma consulta de tabela pivô padrão. Na consulta de tabela pivô dinâmica, os valores da coluna a ser pivotada não são codificados. Em vez disso, os valores da coluna que foi pivotada se expandem/contrain dependendo das condições escolhidas.

No entanto, este tipo não é comumente usado, apesar de suas vantagens. É suscetível a ataques de injeção SQL. Portanto, a recomendação padrão é não usá-lo, mas buscar alternativas, por exemplo, executar a consulta e fazer a pivotação em uma planilha.

A injeção SQL ocorre quando o atacante fornece dados maliciosos que alterarão a semântica do SQL planejado que você está gerando, afetando a forma como será interpretado no sistema.

Portanto, se o usuário não puder inserir valores no momento da execução, então sua consulta de tabela pivô dinâmica é tão segura quanto possível contra ataques de injeção SQL.

No entanto, é possível mitigar os riscos de ataques de injeção SQL, se um usuário precisar inserir valores na consulta de tabela pivô dinâmica no momento da execução.

Algumas maneiras de mitigar os riscos de ataques de injeção SQL são através do uso de:

1) Função QUOTENAME()

2) Consulta parametrizada

3) Usar o operador LIKE com cuidado

4) Revisão de código

5) Usar certificados para assinar procedimentos armazenados



A sintaxe básica para a consulta de tabela pivô dinâmica usando a parametrização é



declare @cols nvarchar(max) = N”

DECLARE @query NVARCHAR(MAX)

select @cols = @cols + case when @cols = N” then QUOTENAME(nome da coluna a pivotar) else N’,’ + QUOTENAME(nome da coluna a pivotar) end

FROM (SELECT DISTINCT (nome da coluna a pivotar) FROM nome da tabela) PV

ORDER BY nome da coluna a pivotar

SET @query = ‘SELECT * FROM

(sua consulta aqui) t

Pivot

((coluna agregada) PARA (nome da coluna a pivotar) EM ( ‘ + @cols + ‘)) as pvt’

EXEC SP_EXECUTESQL @query



1) Função QUOTENAME()

A função QUOTENAME() do SQL Server adiciona delimitadores a uma cadeia de entrada para tornar essa cadeia um identificador delimitado válido do SQL Server, sendo os colchetes quadrados padrão. A declaração de caso é usada com a função QUOTENAME() para remover a vírgula final da cadeia.

A função QUOTENAME() em um valor delimita o valor com um caractere (por exemplo, uma aspa simples) que também pode ser especificado na função. Portanto, mesmo se o valor contiver outros caracteres como aspas ou ponto e vírgula para injetar declarações de SQL prejudiciais, o valor será delimitado e essas declarações de SQL prejudiciais agora farão parte da cadeia a ser pesquisada na tabela em vez de serem declarações de SQL válidas a serem executadas, e assim mitigar o risco de um ataque de injeção SQL. Como a função QUOTENAME() retorna o resultado como uma cadeia Unicode (observe o prefixo N), seu código funcionará corretamente mesmo para nomes de identificadores não padrão. No entanto, a função é apropriada apenas para nomes de objetos como tabelas, colunas, nomes de bancos de dados, etc.

Nota : A função QUOTENAME() retorna NULL se a cadeia de entrada tiver mais de 128 caracteres.

Em nossa sintaxe anterior,