Imagen genérica del Canal Informática y TICS

Creación de informes de referencias cruzadas en SQL Server. El Operador PIVOT.

07/03/2013
Número de visualizaciones

A veces es necesario girar resultados de modo que, los datos en columnas se presentan en sentido horizontal y los datos en filas se presentan en forma vertical. Esto se conoce como la creación de una tabla dinámica ®, creando un informe de referencias cruzadas, o rotación de datos.

En primer lugar vamos a comenzar poniendo un ejemplo de cómo se debía realizar algo similar, aunque con mucha menos potencia en SQL Server 2000, en el que no disponíamos del operador PIVOT y donde teníamos que realizarlo mediante la función CASE.

Supongamos que disponemos de una estructura típica maestro detalle con las tablas Orders, Order Details y deseamos un informe similar al siguiente:

Ventas por año

EneFebMarAbrMayJunJulAgoSepOctNovDic
2000
2001
2002

La información que queremos mostrar, ya agrupada, la podemos obtener con la siguiente instrucción SELECT, aunque no en el formato que la necesitamos mostrar:

select year(O.OrderDate), month(O.OrderDate) as Mes, sum(D.Quantity) as Cantidad
from [Order Details] D inner join Orders O on D.OrderID = O.OrderID
where year(O.OrderDate) > 2000
group by P.ProductName, month(O.OrderDate)
order by 1, 2

El resultado obtenido es:

ProductoMesCantidad
2000197
2000210
2000373
2000434
….....
2001155

Ahora, si queremos mostrarlo según el formato que hemos definido anteriormente, y estamos utilizando SQL Server 2000, tenemos que recurrir a la función CASE, como se muestra a continuación:

select year(O.OrderDate),
sum(CASE WHEN month(O.OrderDate) = 1 THEN D.Quantity ELSE 0 END) AS Ene,
sum(CASE WHEN month(O.OrderDate) = 2 THEN D.Quantity ELSE 0 END) AS Feb,
sum(CASE WHEN month(O.OrderDate) = 3 THEN D.Quantity ELSE 0 END) AS Mar,
sum(CASE WHEN month(O.OrderDate) = 4 THEN D.Quantity ELSE 0 END) AS Abr,
sum(CASE WHEN month(O.OrderDate) = 5 THEN D.Quantity ELSE 0 END) AS May,
sum(CASE WHEN month(O.OrderDate) = 6 THEN D.Quantity ELSE 0 END) AS Jun,
sum(CASE WHEN month(O.OrderDate) = 7 THEN D.Quantity ELSE 0 END) AS Jul,
sum(CASE WHEN month(O.OrderDate) = 8 THEN D.Quantity ELSE 0 END) AS Ago,
sum(CASE WHEN month(O.OrderDate) = 9 THEN D.Quantity ELSE 0 END) AS Sep,
sum(CASE WHEN month(O.OrderDate) = 10 THEN D.Quantity ELSE 0 END) AS Oct,
sum(CASE WHEN month(O.OrderDate) = 11 THEN D.Quantity ELSE 0 END) AS Nov,
sum(CASE WHEN month(O.OrderDate) = 12 THEN D.Quantity ELSE 0 END) AS Dic
from [Order Details] D inner join Orders O on D.OrderID = O.OrderID
where O.OrderDate between '20000101' and '20101231'
group by year(O.OrderDate)
order by 1

El resultado obtenido es:

 Ene Feb Mar  Abr May Jun  Jul Ago  Sep Oct Nov Dic

2000   97 10 0    0  73  34  100   0   30  55  20 108

2001  50   0  20    0  60   0   14   0    0   6  20  20

2002      52  20  61   30  35  52   42  60  144   0  99   1

2003  60   0  60  106  35  24  125  30  135  10  65  15

Si ya disponemos de SQL Server 2005 o posteriores, entonces tendremos disponible una nueva funcionalidad que nos permite realizar de forma más sencilla este tipo de tareas. Esta funcionalidad nos la ofrece el operador PIVOT (en otra ocasión hablaremos del operador UNPIVOT). PIVOT nos permite convertir filas en columnas. Lo podemos incluir en la cláusula FROM de nuestras instrucciones SELECT.

select anio, [1] Ene, [2] Feb, [3] Mar, [4] Abr, [5] May, [6] Jun,
[7] Jul, [8] Ago, [9] Sep, [10] Oct, [11] Nov, [12] Dic
from (
-- select inicial, a pivotar. Podría ser una tabla
select year(O.OrderDate) as anio, month(O.OrderDate) as Mes,
D.Quantity as Cantidad
from [Order Details] D inner join Orders O on D.OrderID = O.OrderID
where O.OrderDate between '20000101' and '20101231'
) V PIVOT ( sum(Cantidad) FOR Mes IN ([1], [2], [3], [4], [5],
[6], [7], [8], [9], [10], [11], [12]) ) as PT

Como podéis comprobar, simplemente tenemos que usar la función PIVOT, a la cual le indicamos una función de agregado, la columna que queremos girar (pivotar) y desglosamos las columnas en las que queremos mostrar la información, esto nos permite utilizar ese desglose realizado como cualquier otra columna más en nuestra instrucción SELECT. La función PIVOT nos permite girar (pivotar), convirtiendo los valores únicos de una columna en varias columnas de salida, y realizando agregaciones para dichos valores.

0
Categorizado en: Redes, Sistemas y Seguridad

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Descubre MasterClass de INESEM. Una plataforma en la que profesionales enseñan en abierto

Profesionales enseñando en abierto