Convertir filas en columnas y columnas en filas en SQL Server

En este post quiero hablar de los operadores relacionales PIVOT y UNPIVOT de SQL Server los cuales en una consulta SQL permiten intercambiar los resultados de filas por columnas (referencias cruzadas).

Aunque en este post nos centramos en SQL Server estos operadores están disponibles en otros gestores de Base de Datos como Oracle.

PIVOT

A través de este operador de SQL Server, podemos transformar las filas devueltas mediante una consulta en columnas de valor único.

operador pivot

La sintaxis del este operador sería:

sintaxis pivot

Veamos un ejemplo de uso. Supongamos que disponemos de los siguientes datos:

datos pivot

Al tomar estos datos como entrada, aplicando el operador PIVOT:

datos pivot

Los resultados obtenidos serían:

resultados obtenidos en pivot Como podemos observar, se hace una agrupación por años, aplicando la función de agrupación SUM y los valores únicos de los años se emplean en la salida como columnas.

Si pidiésemos años para los que no existen valores, en las celdas correspondientes se devolvería NULL.

sentencia bis PIVOT

UNPIVOT

Para realizar la operación inversa, es decir transformar columnas en filas, usamos el operador de SQL Server llamado UNPIVOT.

Para ver el funcionamiento de UNPIVOT, supongamos que usamos como datos de entrada la siguiente tabla:

tabla de datos unpivot

Si aplicamos el operador UNPIVOT a través de la sentencia:

unpivot sentencia

Los resultados obtenidos serían:

columna unpivot

Que como se puede observar, muestra los datos intercambiando las columnas y cruzando las columnas con las filas originales para obtener la tabla completa.

Por tanto UNPIVOT es el operador opuesto a PIVOT, a excepción que PIVOT ejecuta una función de agregación, mientras transforma los valores de la fila en valores de columna, con lo cual podría fusionar lo valores de múltiples filas en un valor de una sola columna de salida. Por tanto, aplicando a unos datos el operador PIVOT y posteriormente el UNPIVOT, no obtendríamos los datos originales.

En cualquier caso estos dos operadores de SQL Server, como he mostrado a lo largo del post, son muy fáciles de usar y con una sola consulta nos pueden dar mucha información.

Categorizado en: Informática y TICS

No hay comentarios

Comentarios
Pingbacks0

EMMANUEL dice:
11/04/2024 a las 4:57 AM
Gracias me ayudo mucho, tengo una duda.
Si en ves de una funcion agregada necesito dos, es decir dos SUM ya que tendo dos columna de valores?
Responder
- Rafael Marín dice:
  11/04/2024 a las 9:25 AM
  Buenas Emmanuel.
  Muchas gracias por su feedback, me alegra que el artículo le haya sido de utilidad.
  En cuanto a su duda, para convertir filas en columnas y viceversa en SQL Server cuando necesitas aplicar dos funciones de agregación diferentes, como dos SUM, puedes utilizar una combinación de la cláusula CASE junto con las funciones de agregación. Aquí te muestro un ejemplo de cómo podrías hacerlo:
  Supongamos que tienes una tabla llamada Datos con tres columnas: Categoria, Valor1 y Valor2. Quieres convertir las filas en columnas, calculando la suma de Valor1 y Valor2 por cada categoría. Puedes hacerlo así:
  SELECT
  Categoria,
  SUM(CASE WHEN Condicion THEN Valor1 ELSE 0 END) AS Suma_Valor1,
  SUM(CASE WHEN Condicion THEN Valor2 ELSE 0 END) AS Suma_Valor2
  FROM
  Datos
  GROUP BY
  Categoria;
  En este ejemplo, Condicion podría ser alguna condición que desees aplicar para determinar qué filas se suman y cuáles no. Podría ser algo como Categoria = ‘AlgunaCategoria’.
  Este SQL sumará los valores de Valor1 y Valor2 por cada categoría, pero solo sumará aquellos que cumplan con la condición especificada en la cláusula CASE. Si necesitas aplicar diferentes condiciones para cada función SUM, simplemente ajusta la condición dentro de la cláusula CASE en cada línea correspondiente.
  Espero haberte ayudado.
  Un saludo.
  Responder
Diego dice:
04/11/2023 a las 11:06 AM
Dado que parece que no existen dos filas en la tabla original para el mismo país y año, ¿por qué usar SUM? ¿ no podemos usar simplemente PIVOT y dejar que el sistema reordene las filas? ¿Es obligatorio usar una función de agregación incluso si uso la sentencia PIVOT?
Muchas gracias de antemano.
Responder
- Rafael Marín dice:
  15/11/2023 a las 5:55 PM
  Buenas tardes Diego.
  En SQL Server, la función PIVOT se utiliza para transformar filas en columnas, pero requiere una función de agregación, ya que necesita saber cómo combinar los valores de las filas correspondientes a una misma columna en la salida pivotada.
  Cuando realizas un PIVOT, estás especificando qué columna se usará para crear las nuevas columnas en la salida. Además, debes proporcionar una función de agregación para indicar cómo se deben combinar los valores de esas filas en las nuevas columnas. La función de agregación puede ser SUM, MAX, MIN, AVG, entre otras.
  Por ejemplo, si tienes varias filas para el mismo país y año y deseas combinar esas filas en una sola fila, necesitas una función de agregación para determinar cómo se calculará el valor resultante. Si no tienes duplicados y sabes que cada combinación de país y año es única, aún así debes especificar una función de agregación en el PIVOT.
  En resumen, aunque no haya duplicados en los datos originales, la función de agregación es necesaria cuando usas PIVOT en SQL Server para indicar cómo se deben combinar los valores de las filas correspondientes a una misma columna en la salida pivotada.
  Responder
Agus dice:
14/10/2021 a las 12:34 PM
MIL GRACIAS
Me ha sido muy útil esta página.
Responder
Elmo dice:
23/08/2021 a las 6:35 PM
Los ejemplos están algo «tontos», cómo voy a saber qué resultados poner luego del FOR? no puedo poner «quemados» valores en una consulta dinámica, a quién se le ocurre?
Responder
Ysidro dice:
23/05/2021 a las 3:57 AM
Saludos hermanos ando investigando, como generar un valor mas alto de una serie de valores en una tabla con php
Responder