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.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT * FROM ( SELECT YEAR(SOH.OrderDate) as SalesYear, SOH.SubTotal as TotalSales FROM sales.SalesOrderHeader SOH JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId ) AS Sales PIVOT (SUM(TotalSales) FOR SalesYear IN ([2011],[2012],[2013],[2014])) as PVT |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT SalesYear, TotalSales FROM ( SELECT * FROM ( SELECT YEAR(SOH.OrderDate) AS SalesYear, SOH.SubTotal AS TotalSales FROM sales.SalesOrderHeader SOH JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId ) AS Sales PIVOT(SUM(TotalSales) FOR SalesYear IN([2011], [2012], [2013], [2014])) AS PVT ) T UNPIVOT(TotalSales FOR SalesYear IN([2011], [2012], [2013], [2014])) AS upvt; |
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.
|
1 2 3 4 5 6 7 8 9 |
SELECT YEAR(SOH.OrderDate) AS SalesYear, MONTH(SOH.OrderDate) AS SalesMonth, SUM(SOH.SubTotal) AS TotalSales FROM sales.SalesOrderHeader SOH JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId GROUP BY YEAR(SOH.OrderDate), MONTH(SOH.OrderDate) ORDER BY YEAR(SOH.OrderDate), MONTH(SOH.OrderDate); |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT SalesYear, ISNULL([Q1], 0) AS Q1, ISNULL([Q2], 0) AS Q2, ISNULL([Q3], 0) AS Q3, ISNULL([Q4], 0) AS Q4, (ISNULL([Q1], 0) + ISNULL([Q2], 0) + ISNULL([Q3], 0) + ISNULL([Q4], 0)) SalesYTD FROM ( SELECT YEAR(SOH.OrderDate) AS SalesYear, CAST('Q'+CAST(DATEPART(QUARTER, SOH.OrderDate) AS VARCHAR(1)) AS VARCHAR(2)) Quarters, SOH.SubTotal AS TotalSales FROM sales.SalesOrderHeader SOH JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId ) AS Data PIVOT(SUM(TotalSales) FOR Quarters IN([Q1], [Q2], [Q3], [Q4])) AS pvt ORDER BY SalesYear; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
SELECT SalesYear, ISNULL([1], 0) AS Jan, ISNULL([2], 0) AS Feb, ISNULL([3], 0) AS Mar, ISNULL([4], 0) AS Apr, ISNULL([5], 0) AS May, ISNULL([6], 0) AS Jun, ISNULL([7], 0) AS Jul, ISNULL([8], 0) AS Aug, ISNULL([9], 0) AS Sep, ISNULL([10], 0) AS Oct, ISNULL([11], 0) AS Nov, ISNULL([12], 0) AS Dec, (ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0) + ISNULL([4], 0) + ISNULL([4], 0) + ISNULL([5], 0) + ISNULL([6], 0) + ISNULL([7], 0) + ISNULL([8], 0) + ISNULL([9], 0) + ISNULL([10], 0) + ISNULL([11], 0) + ISNULL([12], 0)) SalesYTD FROM ( SELECT YEAR(SOH.OrderDate) AS SalesYear, DATEPART(MONTH, SOH.OrderDate) Months, SOH.SubTotal AS TotalSales FROM sales.SalesOrderHeader SOH JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId ) AS Data PIVOT(SUM(TotalSales) FOR Months IN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) AS pvt; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
DECLARE @dml AS NVARCHAR(MAX) DECLARE @ColumnName AS NVARCHAR(MAX) SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Months) FROM (SELECT DISTINCT DATEPART(MONTH, SOH.OrderDate) Months FROM sales.SalesOrderHeader SOH JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId GROUP BY YEAR(SOH.OrderDate), DATEPART(MONTH, SOH.OrderDate)) AS Months --Prepare the PIVOT query using the dynamic SET @dml = N'SELECT SalesYear, ' +@ColumnName + ' FROM ( SELECT YEAR(SOH.OrderDate) AS SalesYear, DATEPART(MONTH, SOH.OrderDate) Months, SUM(SOH.SubTotal) AS TotalSales FROM sales.SalesOrderHeader SOH JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId GROUP BY YEAR(SOH.OrderDate), DATEPART(MONTH, SOH.OrderDate)) AS T PIVOT(SUM(TotalSales) FOR Months IN (' + @ColumnName + ')) AS PVTTable' --Print @DynamicPivotQuery --Execute the Dynamic Pivot Query EXEC sp_executesql @dml |
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 …
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
- Descripción general de la función SQL CAST y SQL CONVERT - December 6, 2019
- Revisión del operador relacional y descripción general dePivot y Unpivot estático y dinámico de SQL - November 6, 2019
- Revisión, ejemplos y uso de SQL Union - November 4, 2019