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.
