2014/12/15

FILAS A COLUMNAS Y VICEVERSA CON PIVOT UNPIVOT.

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:
  1. Una columna de agrupación.
  2. Una columna donde están los nombres de nuestras futuras columnas.
  3. 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:
  1. Un nombre de columna a lo que anteriormente eran nuestras columnas( [1],[2],[3],etc)
  2. 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