Una práctica recurrente en
nuestro diario andar, es la conversión de filas a columnas o columnas a filas,
antes esto lo teníamos que hacer implementando ciertos trucos con cursores o
con la clausula CASE WHEN o con algunos otros, afortunadamente hoy ya contamos
con la ayuda de PIVOT y UNPIVOT.
Trabajaremos sobre la base AdventureWorks2012 y con el siguiente
query:
SELECT YEAR( OrderDate ) AS yr , MONTH( OrderDate ) as mn , TotalDue FROM sales.SalesOrderHeader WHERE OrderDate >= '20060101' AND OrderDate < '20090101'
Con la consulta anterior obtenemos las ventas realizadas entre el
periodo correspondiente, aun no los agrupamos por año y mes, pero si obtenemos
esos valores de cada venta para hacer la agrupación.
Para lograr un PIVOT requerimos
de 3 cosas:
- Una columna de agrupación.
- Una columna donde están los nombres de nuestras futuras columnas.
- Una columna donde se encuentran los valores de las intersecciones de las 2 anteriores, a la cual sea factible aplicar una función de agregado( MAX, MIN, AVG, SUM, COUNT, etc ).
Por cuestiones de buenas prácticas se recomienda una estructura como
la siguiente:
WITH cte AS ( SELECT < 1 >, < 2 >, < 3 > FROM < tablaFuente > ) SELECT < columnas > FROM cte PIVOT( < funcion de agregado > ( < 3 > ) FOR < 2 > IN (< valores contenidos en 2 >) ) AS pt;
Ahora solo aplicamos nuestro query:
;WITH cte AS( SELECT YEAR( OrderDate ) AS yr , MONTH( OrderDate ) as mn , TotalDue FROM sales.SalesOrderHeader WHERE OrderDate >= '20060101' AND OrderDate < '20090101' ) SELECT * FROM cte PIVOT( SUM( TotalDue ) FOR mn IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) AS pt; GO
UNPIVOT
Se podría decir que es el proceso que revierte el PIVOT a su estado original, lo digo así porque recuerden que los
datos ingresados para realizar el PIVOT del query anterior no estaban agrupados
y una vez que apliquemos el UNPIVOT aquí los regresaremos como si estuvieran
agrupados por año y mes.
Necesitamos identificar al menos 2 puntos importantes:
- Un nombre de columna a lo que anteriormente eran nuestras columnas( [1],[2],[3],etc)
- Un nombre de columna para el valor contenido dentro de las columnas anteriores.
Una vez hecho esto, aplicamos lo siguiente:
SELECT < columna fija >, < 1 >, < 2 > FROM < tabla fuente> UNPIVOT( < 2 > FOR < 1 > IN( < nombre de las columnas pivoteadas > ) ) AS U;
Utilizaremos la consulta que hemos trabajado, solo que ahora
utilizaremos otro cte:
;WITH cte AS( SELECT YEAR( OrderDate ) AS yr , MONTH( OrderDate ) as mn , TotalDue FROM sales.SalesOrderHeader WHERE OrderDate >= '20060101' AND OrderDate < '20090101' ) , ctePivot AS( -- APLICANDO PIVOT SELECT * FROM cte PIVOT( SUM( TotalDue ) FOR mn IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) AS pt ) --aplicando UNPIVOT SELECT yr, mes, valor FROM ctePivot UNPIVOT ( valor FOR mes IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) AS unpvt;
Realmente no es tan complicado utilizar ambas sentencias, es cuestión
de practicar y jugar un poco con ellas para comprender correctamente su
funcionamiento.
Pueden dejar su comentario, duda, sugerencia o aclaración en el
apartado de abajo.
SALUDOS
COMPARTE ESTA INFORMACION SI TE PARECIO INTERESANTE
0 comentarios:
Publicar un comentario