Guía para crear consultas de tabla pivote estándar y dinámica en SQL

Hello everyone,

When viewing a query report, there are times when one may wish to transpose rows into columns. One way is to copy paste it to a spreadsheet and do the pivoting there. Another way is to create a pivot query in SQL.

There are two ways of creating a pivot query in SQL.

a) Consulta de tabla pivote estándar/estática

b) Consulta de tabla pivote dinámica

a) Consulta de tabla pivote estándar/estática

En este tipo, la lista de valores distintos en una columna que se va a pivotar está fija.

La sintaxis básica es

Select * from

(tu consulta aquí) t

Pivot

((columna agregada) PARA (columna pivoteada) EN ( [valor 1],[valor 2],..[valor n])) as pvt

En la consulta de tabla pivote estándar/estática anterior, debes escribir los valores distintos de las columnas a pivotar después del operador IN.

Por lo tanto, si se agrega un nuevo valor a la columna a pivotar, pero no se codifica en el operador IN de la consulta de operador pivot, entonces ese valor no aparecerá en el resultado pivotado. Debido a esta limitación, muchos prefieren simplemente ejecutar la consulta normal y hacer el pivote en una hoja de cálculo.

b) ) Consulta de tabla pivote dinámica

Este tipo supera la limitación de columnas fijas de una consulta de tabla pivote estándar. En la consulta de tabla pivote dinámica, los valores de la columna a pivotar no están codificados. Más bien, los valores de la columna que se ha pivotado se expanden/contraen dinámicamente dependiendo de las condiciones elegidas.

Pero este tipo no se usa comúnmente, a pesar de sus ventajas. Es susceptible a ataques de inyección SQL. Por lo tanto, la recomendación estándar es no usarlo, sino buscar alternativas, por ejemplo, ejecutar la consulta y hacer el pivote en una hoja de cálculo.

La inyección SQL ocurre cuando el atacante proporciona datos maliciosos que cambiarán la semántica del SQL previsto que estás generando, afectando la forma en que será interpretado en el sistema.

Por lo tanto, si al usuario no se le permite ingresar valores en el momento de la ejecución, entonces tu consulta de tabla pivote dinámica es tan segura como puede estar libre de ataques de inyección SQL.

Sin embargo, es posible mitigar los riesgos de ataques de inyección SQL, si un usuario debe ingresar valores en la consulta de tabla pivote dinámica en el momento de la ejecución.

Algunas de las formas en las que podemos mitigar los riesgos de los ataques de inyección SQL son a través del uso de:

1) Función QUOTENAME ()

2) Consulta parametrizada

3) Usar el operador LIKE con cuidado

4) Revisión de código

5) Usar certificados para firmar procedimientos almacenados



La sintaxis básica para la consulta de tabla pivote dinámica usando la parametrización es



declare @cols nvarchar (max)= N”

DECLARE @query NVARCHAR(MAX)

select @cols = @cols + case when @cols = N” then QUOTENAME( nombre de la columna a pivotar) else N’,’ + QUOTENAME( nombre de la columna a pivotar) end

FROM    (SELECT DISTINCT (nombre de la columna a pivotar) FROM nombre de la tabla) PV

ORDER BY nombre de la columna a pivotar

SET @query = ‘SELECT * FROM

(tu consulta aquí) t

Pivot

((columna agregada) PARA (nombre de la columna a pivotar) EN ( ‘ + @cols + ‘)) as pvt’

EXEC SP_EXECUTESQL @query



1) Función QUOTENAME ()

La función QUOTENAME() de SQL Server agrega delimitadores a una cadena de entrada para hacer que esa cadena sea un identificador delimitado de SQL Server válido, siendo el predeterminado corchetes cuadrados. La declaración de caso se usa con la función QUOTENAME() para eliminar la coma final de la cadena.

La función QUOTENAME() en un valor delimita el valor con un carácter (por ejemplo, una comilla simple) que también se puede especificar en la función. Por lo tanto, incluso si el valor contiene otros caracteres como comillas o punto y coma para inyectar declaraciones de SQL dañinas, el valor se delimita y esas declaraciones de SQL dañinas ahora formarán parte de la cadena a buscar en la tabla en lugar de ser declaraciones de SQL válidas que se ejecutarán, y por lo tanto mitigar el riesgo de un ataque de inyección de SQL. Como la función QUOTENAME() devuelve el resultado como una cadena Unicode (nota el prefijo N), tu código funcionará correctamente incluso para nombres de identificadores no estándar. Sin embargo, la función solo es apropiada para nombres de objetos como tablas, columnas, nombres de bases de datos, etc.

Nota : La función QUOTENAME() devuelve NULL si la cadena de entrada tiene más de 128 caracteres.

En nuestra sintaxis anterior,