Prashanth Jayaram

Revisión del operador relacional y descripción general de Pivot y Unpivot estático y dinámico de SQL

November 6, 2019 by

En este artículo, efectuaremos una revisión exhaustiva de los operadores de SQL Pivot y SQL Unpivot y además verificar cómo pueden ser útiles para transponer datos de SQL Server. Además, adicionalmente discutiremos las formas estáticas y dinámicas de usar los operadores relacionales PIVOT y UNPIVOT que pueden ser utilizados para transformar valores agregados diferentes como columnas en el conjunto de resultados especificando todos los valores de columna en la cláusula PIVOT IN.

Introducción al Servidor pivote de SQL r

Hay varias formas de transponer e intercambiar un conjunto de datos ya sea de filas a columnas y de columnas a filas. Podemos observar que SQL Pivot es una de las técnicas que permite el intercambio y la transposición de filas a columnas y realiza posibles agregaciones en el camino. SQL PIVOT y SQL UNPIVOT son los operadores relacionales para transponer una serie de datos bidimensionales con valores de tabla en otra forma de datos. SQL PIVOT la que transpone una expresión con valores de tabla de un conjunto único de valores de una columna a varias columnas en la salida y realiza agregaciones. Además, SQL UNPIVOT realiza la operación opuesta de SQL PIVOT transformando una serie de columnas de una expresión con valores de tabla en valores de columna.

Es importante mencionar que, En la mayoría de los casos, la técnica de pivot estático es suficiente para los requisitos comerciales. Por ejemplo, el pronóstico de ventas mensuales, la división de ventas anuales, la agregación de ventas trimestrales, etc., de ahí se puede ver donde las columnas de la cláusula IN permanecen estáticas. En algunos otros casos, necesitamos detalles granulares y la expresión de valor de tabla es de naturaleza más dinámica y todo el tiempo se incluye un nuevo conjunto de expresiones en la tabla, entonces PIVOT dinámico sería la mejor opción.

Nota: Usted puede observar que, en la descripción anterior, podemos verificar que en el proceso de PIVOT, los valores de la columna se rotan de vertical a horizontal y UNPIVOTING es como rotarlo de horizontal a vertical.

Sintaxis

SELECT <non-pivoted column>,

[pivot_column_1] AS <column alias>,

[pivot_column_2] AS <column alias>,

[pivot_column_n] AS <column alias>

FROM

(

<SELECT QUERY>)

AS <Alias for temporary data set>

PIVOT

(

<Aggregate function>( Aggregate column)

FOR

[<Pivot column will become column headers>]

IN ( [pivot_column_1], [pivot_column_2],

… [pivot_column_n] )

) AS <Pivot table alias>

< ORDER BY clause>;

Comenzando con SQL Pivot

En esta sección vamos a poder verificar comenzando a pivotar y des pivotar los datos. A estas alturas del proceso usted ya comprende qué operadores PIVOT y UNPIVOT están en SQL Server. En términos simples, es solo otra forma de describir el proceso de convertir valores de fila distintos en sus propias columnas que se conoce como Pivotar. Y el proceso de convertir columnas en filas se conoce como un no pivote (UNPIVOT).

Ejemplo 1: cómo realizar una operación de pivote de SQL

Veamos en la práctica que un conjunto de datos de muestra derivado de la base de datos AdventureWorks2014. En este caso particular nosotros acabamos de obtener un conjunto de datos simple con la columna SalesYear junto con el valor cuantitativo TotalSales.

Ahora, el conjunto de datos está listo para pivotar. Considerando que se tiene SalesYear en la primera columna, TotalSales en la segunda columna. Si usted solicitó transponer los referidos datos, debe tomar cada año distinto en la columna y estas columnas se convierten en el encabezado de las columnas dinámicas. Entonces, 2011 se convierte en la primera columna, 2012 se convierte en su propia columna y así sucesivamente. Los valores reales de la siguiente columna Total sales, se adhieren a los encabezados de las columnas dinámicas. Consecuentemente ahora, la tabla pivotante se vería a continuación.

La siguiente imagen muestra los datos de entrada, la sintaxis de Pivot, SQL Pivot y los resultados de salida.

<>Ejemplo 2: Cómo realizar una operación de Unpivot de SQL

En este ejemplo deberemos observar ahora en la otra dirección, UNPIVOT, que es tan simple como pasar de horizontal a vertical. En este caso, se debería tomar todas esas columnas distintas que están seleccionadas, y luego se convertiría esos encabezados de columna en sus propias filas. PIVOT es como rotar de vertical a horizontal. Y UNPIVOT es como rotar de horizontal a vertical.

En el siguiente ejemplo, podemos ver que el conjunto de datos pivotados se usa para desvincular los valores. Es importante mencionar que la pieza clave aquí es que esto fue fácil de hacer para luego efectuar la transformación porque pudimos crear una tabla dinámica como un paso intermedio y luego hacer la transformación usando la operación de unpivot.


Nota: La operación de pivote de SQL da como resultado la transposición del resultado agregado en la columna, pero mientras que el proceso de unpivot de SQL no es una inversión exacta de la transposición de columnas en los valores segregados de las filas. El operador no dinámico de unpivot no dividirá los resultados agregados.

Ejemplo 3: Cómo obtener ventas trimestrales utilizando la operación de pivote de SQL

Veamos algunos ejemplos adicionales más para comprender mejor los conceptos fundamentales. En este caso, nosotros deberemos ver cómo obtener datos de ventas trimestrales agregadas en función de las ventas trimestrales.

Antes de adelantarse y saltar a la solución, es siempre recomendable el trabajar en la creación de un conjunto de datos para la operación PIVOT.

El siguiente ejemplo se muestran las ventas mensuales agregadas distribuidas durante el año de ventas.

El siguiente resultado expone y muestra la base de datos

Una vez que esté listo con los datos base, usted puede aplicar el operador PIVOT. En el siguiente ejemplo, ahí podemos observar que la cláusula IN acepta un carácter no numérico que se generó en función del valor DATEPART. Consiguientemente, el valor DATEPART se concatena con ‘Q’ y luego los valores se envían a la cláusula IN del operador PIVOT.

El siguiente resultado es una división trimestral de los datos de ventas.

Ejemplo 4: Cómo obtener ventas mensuales usando una operación pivote SQL

Echemos un vistazo a otro ejemplo que nos permitirá observar los datos de división de ventas en función de cada mes. En el siguiente ejemplo, los valores de la cláusula IN se generan como parte de la función DATEPART. Los valores DATEPART se alimentan a la cláusula IN del operador PIVOT.

El siguiente resultado es una división mensual de los datos de ventas.

Ejemplo 5: Cómo obtener ventas mensuales utilizando la operación dinámica de pivote SQL

Podemos en este caso siguiente abordar y verificar la división mensual de los datos de ventas utilizando pivoting dinámico. Hasta ahora, hemos discutido las operaciones de pivote estático. Para convertir una operación de pivote estático en dinámica, debemos eliminar los valores codificados de la cláusula IN. Como primer paso, obtenga la expresión distinta del conjunto de datos y luego prepare una cadena asociando y concatenado todas las expresiones. En el siguiente ejemplo, el @columnname se usa para concatenar toda la expresión. La expresión concatenada se alimenta para pivotar en la cláusula IN. El resto es una conversión simple de SQL estático en un SQL dinámico y llame a @DML usando el procedimiento almacenado sp_executesql.


Eso es todo por ahora…

Resumiendo, el tema

En esta sección Hasta ahora, hemos demostrado los conceptos básicos y varios ejemplos de SQL PIVOT y SQL UNPIVOT.

El ejemplo de prueba está basado en la base de datos adventureworks2014 de SQL Server. Usted puede intentar y probar todas las muestras por sí mismo. Si tiene alguna pregunta, no dude en comentar a continuación …


Prashanth Jayaram
T-SQL

Acerca de Prashanth Jayaram

Soy un experto en tecnologías con más de 11 años de experiencia en tecnologías de base de datos. Soy Microsoft Certified Professional y tengo el respaldo de una Licenciatura en Master en aplicaciones de computadoras. Mi especialidad es el diseño y la implementación de soluciones de alta disponibilidad y la migración de bases de datos multiplataforma. Las tecnologías en las que trabajo actualmente son SQL Server, PowerShell, Oracle y MongoDB. Ver todas las publicaciones de Prashanth Jayaram

4,872 Views