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

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

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:

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.