PROGRAMANDO EN .NET: Pasar filas a columnas con T-SQL: PIVOT() y las cosas que te alegran el día :)

English version

A veces las cosas que parecen complicadas tienen una solución muy fácil pero que no siempre es la primera que se nos ocurre (no es exactamente el principio de la navaja de Ockham, pero se le parece).

Veamos, el caso es que hay una tabla de logs en la que varios procesos van insertando el resultado de su paso por diferentes estados, algo como esto:



Proceso Estado Timestamp

Proceso1  10   2008/12/21 12:22:05.320
Proceso2  10   2008/12/21 12:22:05.358
Proceso1  11   2008/12/21 12:22:06.210
Proceso1  12   2008/12/21 12:22:06.588
Proceso2  11   2008/12/21 12:22:10.100
Proceso2  12   2008/12/21 12:22:10.048
Proceso3  10   2008/12/21 14:30:05.358
Proceso3  11   2008/12/21 14:30:20.052

Para poder hacer un seguimiento de los procesos y así identificar problemas de rendimiento en ciertos momentos: entran muchos procesos a la vez, se hacen copias de seguridad, el servidor se queda sin memoria, ... sacamos una consulta agrupando por proceso y hora.

SELECT proceso, count(*) as peticiones, datepart(hh,Timestamp) as hora
FROM MiLOG with (nolock)
GROUP BY proceso, datepart(hh,Timestamp)
ORDER BY 1,3

Con esta consulta teníamos un resultado parecido a este:


Proceso    Peticiones Hora
Proceso1   10         12
Proceso1   2          13
Proceso2   5          12
Proceso2   3          13
Proceso3   25         14
Proceso3   4          15

Pero claro, esta salida es un poco complicada de tratar porque aunque estén agrupados por el proceso y la hora cuando hay muchos datos se vuelve muy farragoso comprobarlos, y acabamos llevando los resultados a una hoja de cálculo para que se viesen más claro. Entonces me pregunté, ¿no habrá una forma de hacerlo desde T-SQL para que me convierta las filas en columnas? Vamos, que haya una columna por cada hora y una única fila para cada proceso.

Como siempre la primera solución en una consulta complicada es una sub-consulta (no sé porqué pero es así, alguien tendría que estudiarlo :P) pero en este caso no se trataba de una sub-consulta, sino más bien de 24 sub-consultas, todas atacando a la misma tabla y todas usando la función datepart(hh,Timestamp). Seguro que con eso obtendríamos en resultado esperado, pero a costa de un rendimiento muy muy pobre.

Como esta situación es muy común tenía que haber una forma fácil que solucionarlo, así que en una primera búsqueda de Google no me sorprendió los primeros resultados mostrasen la misma solución de las sub-consultas (por el principio de que la primera solución en una consulta complicada es una sub-consulta :P) ya me veía haciendo una mega-consulta con sub-consultas, JOINs sobre la misma tabla y demás, pero ya en la primera página de resultados encontré una solución mucho más simple: la cláusula PIVOT (que es una novedad SQL Server 2005, así que no lo intentéis con versiones anteriores).

Así que con una consulta como esta:

WITH Procesos( proceso, peticiones, hora)
AS(
SELECT proceso, count(*) as peticiones, datepart(hh,Timestamp) as hora
FROM MiLOG with (nolock)
GROUP BY proceso, datepart(hh,Timestamp)
)
SELECT * FROM Procesos PIVOT(SUM(peticiones) FOR Hora IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])) AS Resultado
order by proceso

sí que tenemos el resultado esperado:



Proceso   0   1   2 ... 21  22  23

Proceso1  0  10  23      4   5  21
Proceso2 12  11  15     22  17   8
Proceso3  9   7   2     19   9  13

Como curiosidad decir que la cláusula PIVOT() únicamente acepta funciones de agregado (COUNT, SUM, MAX, AVG, ...), lo cual es totalmente lógico.

Otra cosa, igual os extraña ese

WITH Procesos( proceso, peticiones, hora) AS(...)

se trata de una expresión de tabla común (CTE) que es una forma de especificar un conjunto de resultados temporal con un nombre que puede ser usado en otras consultas.

Bueno, para empezar el año creo que está bien :D

Happy codding ;)

Bola extra: después de pensar en las sub-consultas y antes de descubrir la cláusula PIVOT, pensé en usar CUBE, que también podía servir, pero no era exactamente lo que buscaba, aquí tenéis un muy buen tutorial sobre CUBE.