El poder del Pivot

¿Alguna vez han tenido el requerimiento de mostrar datos en columnas como meses, o que los socios de negocios aparezcan como columnas en vez de filas?

Bueno si ese es su problema, la función PIVOT es su solución.

El pivot no es simple de usar, en lo personal me costó bastante realizar uno simple satisfactoriamente y mucho más uno dinámico.

El siguiente documento espero les aclare algunas dudas sobre como deben utilizar la función y cuando deben utilizar uno simple o uno dinámico

El pivot básicamente pasa las filas a columnas, para un pivot simple deben declarar en el select los datos a pivotear y en el query de donde se obtienen los datos de las tablas debe haber una columna con los mismos datos que declararon en el select para pivotear. A que me refiero con esto, si van a pivotear por mes ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) dentro del query debe haber una columna que contenga esos mismos datos, en este caso un MONTH(DocDate) por ejemplo.

Aquí les dejo un claro ejemplo de lo que les mencione arriba. Fíjense en el campo MONTH(T0.RefDate)'Month'

DECLARE @ANIO SMALLINT
SET @ANIO = (SELECT A.Year FROM dbo.OACP A WHERE A.Year='[%0]') 

SELECT P.[Cuenta],  P.[Nombre], P.[CCosto],
 [1] as [Ene],
 [2] as [Feb],
 [3] as [Mar],
 [4] as [Abr],
 [5] as [May],
 [6] as [Jun],
 [7] as [Jul],
 [8] as [Ago],
 [9] as [Sep],
 [10] as [Oct],
 [11] as [Nov],
 [12] as [Dic]
FROM (
SELECT T0.Account AS Cuenta ,  T1.AcctName AS Nombre, T2.PrcName AS CCosto, MONTH(T0.RefDate)'Month', SUM(T0.Credit-T0.Debit)'CargoAbono'
FROM dbo.JDT1 T0
INNER JOIN dbo.OACT T1 ON T1.AcctCode=T0.Account
LEFT JOIN dbo.OPRC T2 ON T2.PrcCode=T0.ProfitCode
WHERE YEAR(T0.RefDate)=@ANIO AND T1.GroupMask BETWEEN 6 AND 8
GROUP BY T0.Account, T1.AcctName, T2.PrcName, MONTH(T0.RefDate)
) P
PIVOT (
SUM(CargoAbono) 
FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) P
ORDER BY P.[Cuenta], P.[CCosto]

Bien, ya sabemos que podemos transformar filas en columnas con el pivot, ¿pero que pasa si la cantidad de datos que deseamos transformar en columnas son demasiados? como por ejemplo todos los clientes, es imposible declarar 1000 clientes para hacer un pivot. En este caso se necesita usar un pivot dinámico para poder cumplir el requerimiento.

Si ustedes se fijan, cuando hacemos un pivot la sixtax es la siguiente:

PIVOT (<Operador> <campo_1> FOR <campo_2> IN ([dato 1],[dato 2],[dato 3],...)) AS <Alias>

Lo que se necesita es tener los datos a pivotear en linea, encerrados con corchetes y separados por una coma, lo que es posible con una variable NVARCHAR y usando el operador COALESCE.

Si ejecutan el siguiente query verán como queda:

DECLARE @pvt_table NVARCHAR(MAX)
SELECT @pvt_table = COALESCE(@pvt_table + ',[' + CardCode + ']', '[' + CardCode + ']')
FROM OCRD
WHERE CardType = 'C'

SELECT @pvt_table

Muy bien, tenemos ya los datos para hacer un pivot sin necesidad de declarar 1 por 1 los datos.

Pero no es llegar y meter la variable dentro del pivot y que sea todo tan maravillosamente fácil.

Para hacer eso, debemos meter query dentro de una variable NVARCHAR(MAX) y luego ejecutarla con la función EXEC <@variable>

Yo creo que muchos ya han usado ese metodo, en lo personal yo ocupo el EXEC sp_executesql <@variable>.

¿Cual es la diferencia?, bueno ambos ejecutan el query en la variable, pero la diferencia esta en que el sp_executesql admite parametros y el exec no. Es decir puedo agragar parametros al query con variables y luego darle valores a estas usando el sp_executesql (esto lo aprendi mirando los query que ejecuta SAP con el profiler).

Aquí les dejo un ejemplo, quizá no les arroje datos pero lo importante es que verán a sus clientes como columnas

DECLARE @TOP INT, @F_INI DATETIME, @F_FIN DATETIME
SET @TOP=(SELECT TOP 1 A.TransId FROM [dbo].[JDT1] A WHERE A.RefDate>='[%0]' AND A.RefDate<='[%1]')
SET @F_INI='[%0]'
SET @F_FIN='[%1]'


DECLARE @pvt_table NVARCHAR(MAX)
SELECT @pvt_table = COALESCE(@pvt_table + ',[' + CardCode + ']', '[' + CardCode + ']')
FROM OCRD
WHERE CardType = 'C'


DECLARE @Pvt NVARCHAR(MAX)
SET @Pvt = 
     N'
     SELECT *
     FROM (    
           SELECT Y1.ItemCode AS Codigo , A1.ItemName AS Nombre_Item, Y2.ExpDate AS Fecha_Venc,Y1.BatchNum AS #Lote, Y1.WhsCode AS Almacen, A2.WhsName AS Nombre_Almacen, Y1.CardCode,
        ISNULL((SELECT SUM(X.Quantity) FROM IBT1 X WHERE X.ItemCode=Y1.ItemCode AND X.BatchNum=Y1.BatchNum AND X.WhsCode=Y1.WhsCode AND X.CardCode=Y1.CardCode AND X.Direction != 1),0)-ISNULL((SELECT SUM(X.Quantity) FROM IBT1 X WHERE X.ItemCode=Y1.ItemCode AND X.BatchNum=Y1.BatchNum AND X.WhsCode=Y1.WhsCode AND X.CardCode=Y1.CardCode AND X.Direction=1),0) AS Quantity
        FROM IBT1 Y1
        INNER JOIN OITM A1 ON A1.ItemCode=Y1.ItemCode
        INNER JOIN OWHS A2 ON A2.WhsCode=Y1.WhsCode
        INNER JOIN OIBT Y2 ON Y2.BatchNum=Y1.BatchNum AND Y2.ItemCode=Y1.ItemCode
        WHERE (Y1.DocDate>=(@INI) AND Y1.DocDate<=(@FIN))
     ) AS A
     PIVOT (
     SUM(Quantity)
     FOR CardCode IN ('+ @pvt_table +')
     ) AS Pvt
     '

EXEC sp_executesql @pvt, N'@pvt_table NVARCHAR(MAX), @INI DATETIME, @FIN DATETIME',@pvt_table, @F_INI, @F_FIN

Bueno espero que este pequeño documento les haya gustado, si es así denle like o póngale una nota .

Saludos

Felipe