¿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=(SELECTA.Year FROM dbo.OACPAWHEREA.Year='[%0]’) SELECT P.[Cuenta], P.[Nombre], P.[CCosto], [1]as[Enero], [2]as[Febrero], [3]as[Marzo], [4]as[Abril], [5]as[Mayo], [6]as[Junio], [7]as[Julio], [8]as[Agosto], [9]as[Septiembre], [10]as[Octubre], [11]as[Noviembre], [12]as[Diciembre] FROM (SELECT T0.Account ASCuenta, T1.AcctName ASNombre, T2.PrcName ASCCosto, 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 ANDT1.GroupMask BETWEEN6AND8 GROUP BY T0.Account,T1.AcctName,T2.PrcName,MONTH(T0.RefDate)) PIVOT(SUM(CargoAbono) FOR[Month]IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) ORDER BYP.[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 ( <campo_1> FOR <campo_2> IN ([dato 1],[dato 2],[dato 3],…)) AS
Lo que se necesita es tener los datos a pivotear en línea, 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 método, 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 parámetros y el exec no. Es decir puedo agregar parámetros al query con variables y luego darle valores a estas usando el sp_executesql (esto lo aprendí 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 TOP1A.TransId FROM[dbo].[JDT1]AWHEREA.RefDate>='[%0]’ANDA.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 |
Espero les haya gustado y lo tomen como ejemplo para futuros reportes.
