Este artículo brindará una descripción general completa del uso de los operadores PIVOT y UNPIVOT en SQL Server. Los operadores PIVOT y UNPIVOT son similares a los operadores relacionales que permiten transformar la expresión con valores de tabla en otra tabla . Ambos operadores generan informes multidimensionales que ayudan a combinar y comparar una gran cantidad de datos rápidamente.
Podemos usar el Operador PIVOTE cuando necesitamos transformar expresiones con valores de tabla. Se divide el valores únicos de una columna a muchas columnas en el resultado final. También agregados los valores de columna restantes necesarios en el resultado final. Operador UNPIVOT convierte datos de columnas de una expresión con valores de tabla en valores de columna, que es lo inverso de PIVOT.
Entendámoslo con la ayuda del diagrama simple que se muestra a continuación:
freddie mercurio nacido
En el lado izquierdo de esta figura podemos ver la conjunto de datos originales , que tiene tres columnas: Año, Región, y Ventas . A continuación, podemos ver la tabla PIVOT en el lado derecho, que se construye girando el Región (filas) en Norte y Sur (columnas) . Después de convertir filas en columnas, podemos hacer una agregado de valores de la columna Ventas para cada intersección entre las columnas y filas de la tabla PIVOT.
Primero creemos una tabla llamada demostración_pivote para demostrar los operadores PIVOT y UNPIVOT. La siguiente declaración crea una nueva tabla en nuestra base de datos especificada:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
A continuación, inserte algunos datos en esta tabla como se muestra a continuación:
INSERT INTO pivot_demo VALUES ('North', 2010, 72500), ('South', 2010, 60500), ('South', 2010, 52000), ('North', 2011, 45000), ('South', 2011, 82500), ('North', 2011, 35600), ('South', 2012, 32500), ('North', 2010, 20500);
Podemos verificar los datos usando la declaración SELECT. Obtendremos el siguiente resultado:
Operador PIVOTE
Este operador se utiliza para rotar expresiones con valores de tabla. Se introdujo por primera vez en la versión SQL Server 2005. Convierte datos de filas a columnas. Divide los valores únicos de una columna en muchas columnas y luego agrega los valores de columna restantes necesarios en el resultado final.
Debemos seguir los siguientes pasos para crear una tabla PIVOT:
- Seleccione el conjunto de datos base para pivotar.
- Cree resultados temporales con la ayuda de una tabla derivada o CTE (expresión de tabla común).
- Utilice el operador PIVOT.
Sintaxis
La siguiente sintaxis ilustra el uso de PIVOT en SQL Server:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Si dividimos este script, podemos ver que tiene dos secciones separadas. La primera sección selecciona datos de la tabla principal y la segunda sección determina cómo se construirá la tabla PIVOT. La segunda parte también contiene algunas palabras clave especiales como SUM, FOR e IN. Veamos el significado de estas palabras clave en el operador PIVOT.
SUMA
Este operador está acostumbrado a agregar los valores de la columna especificada que se utilizará en la tabla PIVOT. Debemos usarlo con el operador PIVOT para obtener las visualizaciones de columnas agregadas para las secciones de valores.
PARA palabra clave
Esta palabra clave se utiliza para que la declaración de la tabla PIVOT instruir al operador PIVOT en qué columna se debe aplicar la función PIVOT. Básicamente, indica los nombres de las columnas que se transformarán de filas a columnas.
EN palabra clave
Esta palabra clave enumera todos los valores únicos de la columna PIVOT que se mostrarán como las columnas de la tabla PIVOT.
Ejemplo
Entendámoslo con la ayuda de varios ejemplos.
1. La siguiente declaración primero selecciona las columnas Año, Norte y Sur como datos base para la pivotación. Luego, cree un resultado temporal utilizando la tabla derivada y finalmente aplique el operador PIVOT para generar el resultado final. Esta salida también está ordenada en Año ascendente.
SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS Tab2 ORDER BY Tab2.Year
La ejecución de esta declaración producirá el siguiente resultado. Aquí podemos ver la suma calculada de las ventas de las regiones Norte y Sur correspondientes a los valores del año .
2. Este es otro ejemplo donde vamos a calcular la suma de ventas de cada Año correspondiente a los valores de la región:
SELECT Region, 2010, 2011, 2012 FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN (2010, 2011, 2012)) AS Tab2 ORDER BY Tab2.Region;
La ejecución de esta declaración producir un error porque no podemos especificar el valor numérico como nombre de columna directamente.
Sin embargo, SQL Server nos permite evitar este problema usando corchetes antes de cada valor entero. La declaración actualizada se muestra en el siguiente fragmento de código:
SELECT Region, [2010], [2011], [2012] FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN ([2010], [2011], [2012])) AS Tab2 ORDER BY Tab2.Region;
Esta declaración se ejecutó correctamente y muestra la suma calculada de las ventas para cada año correspondiente a los valores de la región:
3. El ejemplo anterior de obtención de una tabla PIVOT es útil cuando conocemos todos los valores posibles de la columna PIVOT. Pero supongamos que el próximo año aumenta el número de columnas. Teniendo en cuenta el ejemplo anterior, tenemos los años 2010, 2011 y 2012 como columnas PIVOT. Sin embargo, no hay garantía de que estas columnas no cambien en el futuro. ¿Qué pasa si tenemos datos de 2013 o 2014, o quizás incluso más? En tales casos, necesitaremos utilizar tabla PIVOT dinámica consultas para solucionar este problema.
La consulta de tabla PIVOT dinámica encapsula todo el script PIVOT en un procedimiento almacenado. Este procedimiento proporcionará opciones ajustables, permitiéndonos modificar nuestros requisitos cambiando algunos valores parametrizados.
cómo devolver una matriz en java
El siguiente código SQL explica el funcionamiento de la tabla PIVOT dinámica. En este script, primero recuperamos todos los valores distintos de la columna PIVOT y luego escribimos una declaración SQL para ejecutar con la consulta PIVOT en tiempo de ejecución. Veamos el resultado después de ejecutar este script:
CREATE PROCEDURE DynamicPivotTable @PivotColumn NVARCHAR(255), @PivotList NVARCHAR(255) AS BEGIN DECLARE @Query NVARCHAR(MAX); SET @Query = N' SELECT * FROM (SELECT [Region], [Year], [Sales] FROM pivot_demo) AS tab1 PIVOT (SUM([Sales]) FOR ['+@Pivot_Column+'] IN ('+@Pivot_List+')) AS PivotTable'; EXEC(@Query) END
En este script, hemos creado dos variables parametrizadas. Su descripción se da a continuación:
@PivotColumn : Esta variable tomará el nombre de la columna de la tabla original en la que se crea la tabla PIVOT. Por ejemplo , aquí, la columna 'Región' muestra todas las regiones disponibles en las columnas.
@Lista dinámica : Esta variable tomará la lista de columnas que queremos mostrar como columna de salida en la tabla PIVOT.
Ejecución de procedimiento almacenado dinámico
Después de la creación exitosa del procedimiento almacenado dinámico, estamos listos para ejecutarlo. La siguiente declaración se utiliza para llamar al procedimiento almacenado dinámico para mostrar la tabla PIVOT en tiempo de ejecución:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Aquí hemos especificado el nombre de la columna ' Región ' como primer parámetro y la lista de columnas PIVOT como segundo parámetro. Al ejecutar el script se mostrará el siguiente resultado:
miflixer
Ahora, podemos agregar más columnas en el futuro en tiempo de ejecución para mostrar la tabla PIVOT, lo cual no es posible con los dos primeros ejemplos.
Operador UNPIVOT
Es el método inverso del operador PIVOT en SQL Server. Mans este operador realiza la operación opuesta de PIVOT convirtiendo datos de columnas a filas. El operador UNPIVOT también gira la tabla PIVOT a la tabla normal. Se introdujo por primera vez en la versión SQL Server 2005.
Sintaxis
La siguiente sintaxis ilustra UNPIVOT en SQL Server:
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Ejemplo
Entendamos cómo DESACTIVAR la operación PIVOTAR con ejemplos. Primero crearemos un mesa original y mesa PIVOT y luego aplicó el operador UNPIVOT en esta tabla.
El siguiente fragmento de código primero declara una variable de tabla temporal @Tab:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
A continuación, insertaremos valores en esta tabla como se muestra a continuación:
INSERT INTO @Tab SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ORDER BY PivotTable.Year
Ahora, podemos realizar la operación UNPIVOT usando la siguiente declaración:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
La ejecución del fragmento de código devolverá el siguiente resultado:
El siguiente fragmento de código es otro ejemplo para realizar primero la operación PIVOT y luego la operación DESPIVOT en la misma tabla dentro de una sola consulta:
SELECT Region, Year, Sales FROM ( SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ) P --Perform UNPIVOT Operation UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Al ejecutar el fragmento de código se mostrará el mismo resultado:
NOTA: El proceso UNPIVOT es una operación inversa del procedimiento PIVOT, pero no es una inversión exacta. Debido a que las filas se fusionaron cuando PIVOT calcula el agregado y combinaron muchas filas en una sola fila en el resultado, la operación UNPIVOT no puede hacer que la tabla sea como la original. Sin embargo, si el operador PIVOT no combina muchas filas en una sola fila, entonces el operador UNPIVOT puede obtener la tabla original de la salida PIVOT.
Conclusión
Este artículo brindará una descripción completa de los operadores PIVOT y UNPIVOT en SQL Server y convertirá una expresión de tabla en otra. Nunca se debe olvidar que UNPIVOT es la operación inversa de PIVOT, pero no es exactamente la inversa del resultado de PIVOT.