Pivot y Unpivot en SQL Server 2005

Introducción

Hace unas semanas recibí un mail de mi amigo Guille, en el que me pedía una colaboración para la celebración del 10º aniversario de su sitio. A lo que no me pude negar. Aquí os copio un extracto:

“Este mensaje es para preguntarte si te gustaría escribir un artículo "especial" para el 10º aniversario de mi sitio.

El aniversario, como seguramente ya sabrás, es el próximo mes de Noviembre, pero no tienes porqué escribirlo con tan poco tiempo, puedes mandarlo cuando te venga bien. El tema puede ser el que quieras...

Confirmame si te interesa o no, cualquiera que sea tu respuesta será bienvenida ;-)))”

Como podéis ver, no me podía negar ante una persona que te pide algo, que sea “lo que quieras y para cuando quieras”, así que aquí me encuentro, sacando un rato en el fin de semana para cumplir mi promesa. Por cierto, acabo de comerme una tostada con aceite, como a ti te gustan :-)))

Felicidades Guille !!!

Bueno, pues ahora vamos al tema.

Pivot y Unpivot en SQL Server 2005

Finalmente me he decidido a escribir sobre una de las novedades que nos proporciona el lenguaje Transact SQL de SQL Server 2005, los operadores relacionales PIVOT y UNPIVOT, que nos permitirán hacer cosas similares a las que se realizan en Access con las Consultas de Referencias Cruzadas, y que todos echáis de menos cuando migráis a SQL Server y necesitáis hacer algo similar.

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 de ellos y teníamos que realizarlo mediante la función CASE.

Supongamos que tenemos que mostrar una columna con las unidades vendidas de cada artículo de nuestro almacén por mes del año. Este es un caso típico que muchas veces hemos almacenado en tablas renormalizadas con acumulados. Aquí os muestro los datos que queremos mostrar:

Año 1997

Para que podáis probar el ejemplo vamos a utilizar la tan utilizada base de datos Northwind (base de datos de ejemplo suministrada con SQL Server 7 y 2000). Utilizaremos las tablas Orders, Order Details y Products (que traducidas serían algo así como Cabecera de pedidos, Detalle de pedidos, y Productos). 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 P.ProductName as Producto, month(O.OrderDate) as Mes, sum(D.Quantity) as Cantidad

from [Order Details] D inner join Orders O on D.OrderID = O.OrderID

                       inner join Products P on D.ProductID = P.ProductID

where year(O.OrderDate) = 1997

group by P.ProductName, month(O.OrderDate)

order by 1, 2

El resultado obtenido es:

Producto          Mes   Cantidad

Alice Mutton      1     97

Alice Mutton      2     10

Alice Mutton      5     73

Alice Mutton      6     34

Alice Mutton      7     100

Alice Mutton      9     30

Alice Mutton      10    55

Alice Mutton      11    20

Alice Mutton      12    108

Aniseed Syrup     1     50

Aniseed Syrup     3     20

Aniseed Syrup     5     60

...

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 P.ProductName as Producto,

 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

                       inner join Products P on D.ProductID = P.ProductID

where O.OrderDate between '19970101' and '19971231'

group by P.ProductName

order by 1

El resultado obtenido es:

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

Alice Mutton      97  10   0    0  73  34  100   0   30  55  20 108

Aniseed Syrup     50   0  20    0  60   0   14   0    0   6  20  20

Boston Crab Meat  52  20  61   30  35  52   42  60  144   0  99   1

Camembert Pierrot 60   0  60  106  35  24  125  30  135  10  65  15

...

Como veis no es algo tan trivial y flexible como en Access, pero con un poquito de trabajo adicional si que se pueden realizar este tipo de consultas.

Si ya disponemos de SQL Server 2005, entonces tendremos disponible una nueva funcionalidad que nos permite realizar de forma más sencilla este tipo de tareas, al ofrecernos los operadores relacionales PIVOT y UNPIVOT.

PIVOT nos permite convertir filas en columnas. Lo podemos incluir en la cláusula FROM de nuestras instrucciones SELECT.

select Producto, [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 P.ProductName as Producto, month(O.OrderDate) as Mes, 

          D.Quantity as Cantidad

   from [Order Details] D inner join Orders O on D.OrderID = O.OrderID

          inner join Products P on D.ProductID = P.ProductID

      where O.OrderDate between '19970101' and '19971231'

     ) 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. De UNPIVOT poco, más bien nada he hablado, simplemente quiero citar que hace justo lo contrario. Si nos encontramos con la típica tabla de acumulados, con una columna por cada mes podemos convertirla en varias filas, una por cada mes.

Ya para finalizar, quiero comentaros que, a parte de la función PIVOT, que en ciertas ocasiones se puede quedar corta para las tareas que deseáis realizar, y que podéis echar en falta algunas de las características de las referencias cruzadas de Access, en SQL Server 2005, tenéis todo un motor multidimensional con servicios OLAP, llamado Analysis Services 2005. No pretendo en este momento presentar aquí un producto tan amplio y potente como los Analysis Services, sino que simplemente quiero dejar una referencia. Y si alguien quiere comenzar a ampliar información, puede comenzar por la sección que tengo dedicada a este tema en mi sitio (www.helpdna.net), concretamente en la sección dedicada a este tema:

http://www.helpdna.net/sqlserver_olap_datawarehouse_analysis_services.htm