Introducción
en mi último artículo «convertir valores separados por comas a filas y viceversa en SQL Server», hablé sobre cómo puede convertir valores separados por comas (o separados con algún otro carácter) en una sola columna en filas y viceversa. En este artículo, muestro cómo puede convertir valores de filas en valores de columnas (PIVOT) y valores de columnas en valores de filas (UNPIVOT) en SQL Server.,
convertir filas en columnas: PIVOT
SQL Server tiene un operador relacional PIVOT para convertir los valores únicos de una columna especificada de varias filas en valores de varias columnas en la salida (pestaña cruzada), rotando efectivamente una tabla. También permite realizar agregaciones, donde sea necesario, para los valores de columna que se esperan en el resultado final. La sintaxis básica para un operador relacional pivote se ve así:
con el script a continuación, vamos a crear una tabla y cargar algunos datos en ella., Como puede ver en la imagen de abajo, tiene información de ventas para algunos países durante un par de años. Además, si te das cuenta, para cada país y para cada año hay una fila separada.
ahora, utilizando el operador de pivote, convertiremos los valores de fila en valores de columna con el script que se muestra a continuación y los resultados como se muestra en la imagen de abajo.
aunque hemos utilizado la función de agregación de suma, en este caso no hay suma, ya que solo hay una fila para cada combinación única para país y Año., Tenga en cuenta el uso de corchetes para los valores de columna pivote; estos son obligatorios.
aportando dinamismo al pivote
Si observa en el script anterior, hemos proporcionado valores (2005, 2006, 2007 y 2008) para las columnas pivote, ya que estos valores están disponibles en los conjuntos de datos originales. Pero qué pasa si se espera que lleguen algunos valores adicionales en el futuro, por ejemplo, 2009 y 2010, etc. para la columna pivote?,
en ese caso, aún puede usar los valores de columna pivote, que se espera que vengan (o que aún no están disponibles en el conjunto de datos original) en el futuro, aunque verá NULL para sus valores. El script de abajo muestra este escenario y la imagen de abajo muestra nulos para los años 2009 y 2010 ya que no hay datos para estos años.
El enfoque discutido anteriormente funciona bien si ya conoce todos los valores posibles para la columna pivote, pero ¿qué pasa si no lo hace?,
en ese caso, puede escribir una consulta dinámica para capturar primero todos los valores únicos para la columna pivot en tiempo de ejecución y luego una consulta dinámica de escritura para ejecutarla con la consulta pivot como se muestra a continuación:
convertir columnas en filas – UNPIVOT
UNPIVOT es otro operador relacional en SQL Server que realiza casi la operación inversa de PIVOT, girando los valores de columna en valores de filas. Permítanme demostrar esto con un ejemplo; vamos a crear una tabla con datos dinámicos de la consulta anterior con el script a continuación., La siguiente imagen muestra los datos de la tabla recién creada con datos dinámicos.
para rotar valores de columna en valores de fila, ahora usaremos el operador UNPIVOT como se muestra a continuación. La siguiente imagen muestra los datos rotados:
si hace referencia de nuevo, dije que UNPIVOT es casi el reverso del operador de pivote; esto significa que podría o no ser exactamente el mismo. La razón es que Pivot realiza la agregación mientras gira los valores de fila en valores de columna y puede combinar posibles valores de fila múltiples en un valor de columna única en la salida., Por ejemplo, considere que para un país y Año dados hay dos valores, digamos 5000 y 6000. Ahora, cuando lo pivote, la salida tendrá 11000 como el valor de la columna si tiene SUM como la función de agregación. Más tarde, si desea desempaquetarlo de nuevo, obtendrá 11000 no los valores bifurcados (5000 y 6000 como originales). Habiendo dicho eso, podemos decir que si el pivote valores son valores agregados, no será posible obtener los datos originales.,
conclusión
en este artículo, demostré cómo puede convertir valores de fila en valores de columna (PIVOT) y valores de columna en valores de fila (UNPIVOT) en SQL Server. También hablé de escribir una consulta dinámica para escribir una consulta dinámica de pivote en la que los posibles valores de columna de pivote se determinan en tiempo de ejecución.