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 ( <campo_1> FOR <campo_2> IN ([dato 1],[dato 2],[dato 3],…)) AS
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

Espero les haya gustado y lo tomen como ejemplo para futuros reportes.

Saludos

También te podría gustar...

12 Respuestas

  1. Leonel S dice:

    El primer query me funciono de maravilla

  2. Alexis Alvarado dice:

    Buenos dias. Tengo el codigo similar al tuyo, pero al ejecutarlo me sale «que debo declarar la variables escalar @categoria»..
    Podrias ayudarme?

    declare @columnas nvarchar(100);
    declare @sqlstring nvarchar (max);
    declare @Prmfecha_inicio date;
    declare @Prmcategoria varchar(100);
    set @Prmfecha_inicio = ’22/06/2016′;
    set @Prmcategoria = ‘radios’;
    set @columnas= N»;
    select @columnas = coalesce(@columnas + ‘[‘ + cast(id_interno as varchar(12)) +’],’,»)
    from (select distinct dp.id_interno from Detalle_Pedido dp inner join articulos a on a.codigo=dp.codigo_articulo where cantidad>0) as col;
    set @columnas = left (@columnas,LEN(@columnas)-1);
    set @sqlstring= N’
    select * from (
    select f.fecha, a.categoria,dp.id_interno,a.codigo,dp.cantidad
    from factura f inner join detalle_factura df on f.numero_factura=df.numero_factura and f.sucursal=df.mano_obra inner join
    articulos a on a.codigo=df.codigo_articulo left join detalle_pedido dp on a.codigo=dp.codigo_articulo
    ) as subquery
    pivot (
    sum (cantidad) for id_interno in (‘ + @columnas + ‘)
    ) as pvt
    where fecha= @fecha_inicio or fecha>@fecha_inicio and categoria=@categoria ;’;
    exec sp_executesql @sqlstring, N’@fecha_inicio date’, ‘@categoria varchar(100)’, @Prmfecha_inicio,@Prmcategoria;

  3. Omar LM dice:

    Hola buena tarde estimado master.

    Mi consulta que me están requiriendo es para las actividades de una llamada de servicio, en la cual desean que cada actividad de la llamada aparezca en cada columna.
    SELECT T0.[SrvcCallId], T0.[ClgID], T1.[BeginTime] FROM SCL5 T0 INNER JOIN OCLG T1 ON T0.ClgID = T1.ClgCode
    Es por ello que queria pedir de su apoyo ya que no conozco mucho del PIVOT o alguna pequeña ayuda.
    De antemano gracias y quedo de usted por cualquier comentario.

    Saludos!!

  4. Omar Obregon dice:

    Hola!
    Hace años te escribí y gracias a ti pude hacer una consulta muy complicada. Ya después pude optimizarla y hacer que todo se hiciera en un procedimiento que se actualizara (en cuestión de las columnas)

    Pero tengo una duda… Actualmente trabajo en otro reporte, y de un pedido a veces se mandan en distintas partidas (hasta 15), ya hice que las partidas aparezcan como columnas y la cantidad enviada se acomoda en su respectiva partida (yey!).
    Mi duda es la siguiente… Siempre que intento poner la fecha, se me acomodan en filas distintas
    Veré si me puedo dar a explicar
    ……|1|.|2|.|3|…|n|
    Venta.|5|.|2|.|4|…|7|

    A mi me gustaría que fuera posible agregar la fecha de cada partida a un lado.

    Tal vez pueda pensar en la solución un poco después, pero ahorita mi creatividad se ha acabado

    Muchas gracias por todo 😀

  5. Andrea Silva dice:

    Hola, tengo un problema en mi consulta
    DECLARE @PRPVT varchar(max), @NPREG nvarchar(max)
    DECLARE @PRID INT
    DECLARE @PVENC INT

    SET @PVENC=3
    SET @PRID = (select min(idpregunta) from enc_pregunta)
    SET @NPREG = (select a.pregunta from enc_pregunta a where a.idpregunta = @PRID)
    SET @PRPVT = N»

    WHILE @NPREG is not null
    BEGIN
    SET @PRPVT = @PRPVT + ‘,[‘ + @NPREG + ‘]’
    SET @NPREG = (select top(1) pregunta from enc_pregunta where idpregunta > @PRID)
    SET @PRID = (select MIN(idpregunta) from enc_pregunta where pregunta = @NPREG)
    END

    PRINT @PRPVT
    SET @PRPVT = SUBSTRING(@PRPVT,2,LEN(@PRPVT))
    PRINT ‘ok’
    PRINT @PRPVT

    DECLARE @SQLPVT nvarchar(max)
    SET @SQLPVT = N’SELECT * FROM(select e.idusuario,a.idusuarioencuesta,e.identificador, e.nombres, e.ap_pat, ISNULL(e.ap_mat, 0) AS ap_mat,c.idestado ,b.pregunta, ISNULL(a.respuesta_int, 0) as respuesta_int from enc_respuesta a
    inner join enc_pregunta b on a.idpregunta=b.idpregunta
    inner join enc_usuario c on a.idusuarioencuesta=c.idusuarioencuesta
    inner join enc_enc_pregunta d on a.idpregunta=d.idpregunta
    inner join emp_usuario e on c.idusuario=e.idusuario where c.idencuesta=’+@idencuesta+’) PIV
    PIVOT (min(respuesta_int) FOR pregunta IN (‘+ @PRPVT +’)) as pregunta’

    EXEC sp_executesql @SQLPVT

    Donde aplico where c.idencuesta=’+@idencuesta+’ me arroja un error Conversion failed when converting the nvarchar value donde corta la consulta dejandola hasta where c.idencuesta=’
    Si pudieran ayudarme con esto seria de gran utilidad!

    • Omar Obregon dice:

      Intenta poniendo otras comillas esto
      c.idencuesta=’’+@idencuesta+’’

      No copies y pegues esto, escribe las comillas

  6. Buenas tardes

    Utiliza este query, cambia los codigos de las bodegas por los tuyos.

    select ItemCode,sum(Sh)Sh,sum(TI)TI,sum(Bod)Bod,sum(ST)ST,sum(PR)PR,sum(CO)CO
    from
    (
    	select
    		ItemCode, 
    		isnull([01],0) Sh,
    		isnull([02],0) TI, 	
    		isnull([03],0) Bod, 	
    		isnull([04],0) ST, 	
    		isnull([05],0) PR, 	
    		isnull([06],0) CO 	
    	from (
    		SELECT T0.[WhsCode], T2.[WhsName], T0.[ItemCode], T1.[ItemName],sum(isnull(M.InQty,0)-isnull(M.OutQty,0))[Saldo]
    		FROM OINM M
    		INNER JOIN OITW T0 ON M.ItemCode=T0.ItemCode AND M.Warehouse=T0.WhsCode
    		INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
    		INNER JOIN OWHS T2 ON T0.WhsCode = T2.WhsCode
    		WHERE M.DocDate <= getdate()
    		group by T0.[WhsCode], T2.[WhsName], T0.[ItemCode], T1.[ItemName]
    	) V
    	pivot 
    	( 
    		sum(V.Saldo)
    		for WhsCode in ([01],[02],[03],[04],[05],[06])
    	) as Pvt
    ) A
    group by ItemCode
    

    Saludos

    • Cristian Ch dice:

      Felipe, muchas gracias, mira que andábamos tan cerca pero como dicen, el que sabe,,, sabe ayer entrándole lo llegue hasta acá
      DECLARE @DATE AS DATETIME;
      SET @DATE = GETDATE ();

      select P.ItemName, P.ItemCode,P.WhsName,
      [100] Sh,
      [101] TI,
      [102] Bod,
      [103] ST,
      [104] PR,
      [199] CO
      from (
      SELECT T0.[WhsCode],T2.[WhsName], T0.[ItemCode], T1.[ItemName],
      (ISNULL(SUM(M.InQty)-SUM(M.OutQty),0)) AS [Stock]
      FROM OINM M WITH (NOLOCK)
      INNER JOIN OITW T0 (NOLOCK) ON M.ItemCode=T0.ItemCode AND M.Warehouse=T0.WhsCode
      INNER JOIN OITM T1 (NOLOCK) ON T0.ItemCode = T1.ItemCode
      INNER JOIN OWHS T2 (NOLOCK) ON T0.WhsCode = T2.WhsCode
      WHERE M.DocDate <= @DATE AND M.ItemCode = '0009'
      GROUP BY T0.WhsCode, T2.WhsName, T0.ItemCode, T1.ItemName
      ) P
      PIVOT (
      SUM(Stock)
      FOR [WhsCode] IN ([100],[101],[102],[103],[104],[199])
      ) P
      ORDER BY P.ItemName, P.ItemCode

      tenia dos lineas que contemplaste y yo no
      select ItemCode,sum(Sh)Sh,sum(TI)TI,sum(Bod)Bod,sum(ST)ST,sum(PR)PR,sum(CONG)CONG

      y la
      ) A
      group by ItemCode –al final del select externo

      Muchas gracias amigo

  7. Cristian Ch dice:

    hola felipe en un post compartiste un query pequeño pero bueno
    SELECT T0.[WhsCode], T2.[WhsName], T0.[ItemCode], T1.[ItemName],
    (ISNULL(SUM(M.InQty)-SUM(M.OutQty),0)) AS [Stock]
    FROM OINM M WITH (NOLOCK)
    INNER JOIN OITW T0 (NOLOCK) ON M.ItemCode=T0.ItemCode AND M.Warehouse=T0.WhsCode
    INNER JOIN OITM T1 (NOLOCK) ON T0.ItemCode = T1.ItemCode
    INNER JOIN OWHS T2 (NOLOCK) ON T0.WhsCode = T2.WhsCode
    WHERE M.DocDate <= GETDATE()
    GROUP BY T0.WhsCode, T2.WhsName, T0.ItemCode, T1.ItemName

    lo he tratade convertir a Pivot pero no lo he logrado, me podrias apoyar ?
    select WhsCode, [100] Sh,[101] TI, [102] Bod, [103] ST, [104] PR, [199] CO from (
    SELECT T0.[WhsCode], T2.[WhsName], T0.[ItemCode], T1.[ItemName]
    FROM OINM M
    INNER JOIN OITW T0 ON M.ItemCode=T0.ItemCode AND M.Warehouse=T0.WhsCode
    INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
    INNER JOIN OWHS T2 ON T0.WhsCode = T2.WhsCode
    WHERE M.DocDate <= getdate())
    V PIVOT –( SUM M.InQty – SUM M.OutQty FOR WhsCode in [100],[101],[102],[104],[199] ) as PT
    ( SUM (InQty – OutQty) FOR WhsCode in ([100],[101],[102],[104],[199] )) as PT
    GROUP BY T0.WhsCode, T2.WhsName, T0.ItemCode, T1.ItemName

  8. Omar Obregon dice:

    Y también si pongo el query exactamente como lo has puesto en SAP me marca error, me dice que está esperando los parametros

    • Hola

      Gracias por el comentario.

      Había un error que pudo ser cuando copie el query a la pagina.

      El error está en el EXEC final donde falta agregar los parametros de fecha.

      Donde dice:
      EXEC sp_executesql @pvt, N’@pvt_table NVARCHAR(MAX), @INI DATETIME, @FIN DATETIME’,@pvt_table

      Debe decir:
      EXEC sp_executesql @pvt, N’@pvt_table NVARCHAR(MAX), @INI DATETIME, @FIN DATETIME’,@pvt_table,@F_INI,@F_FIN

      Con ese cambio ya te funcionará y ya fue corregido en el código que esta posteado.

      Saludos.

  9. Omar Obregon dice:

    Por alguna razón a mi me marca error un query en SQL
    Básicamente es lo mismo, pero no entiendo porque falla 🙁

    —-
    DECLARE @F_INI DATE = ‘20130101’
    DECLARE @F_FIN DATE = ‘20131102’
    DECLARE @PivotColumnHeaders VARCHAR(MAX)
    SELECT @PivotColumnHeaders = COALESCE( @PivotColumnHeaders + ‘,[‘ + cast(internalSN as varchar) + ‘]’, ‘[‘ + cast(internalSN as varchar)+ ‘]’ ) FROM OINS WHERE U_ACARREO = ‘TRAILERS’
    DECLARE @PivotTableSQL NVARCHAR(MAX)
    SET @PivotTableSQL = N’
    SELECT
    *
    FROM
    (SELECT
    T0.DocDate AS [Fecha Contabilización],
    T0.U_CTROCTOEQ AS [Equipo],
    SUM(Quantity) AS [Cantidad],
    (SELECT SUM(Quantity) FROM OWTR T3 INNER JOIN WTR1 T4 ON T3.DocEntry = T4.DocEntry WHERE T3.DocDate = T0.DocDate GROUP BY T3.DocDate) AS [Total]
    FROM
    OWTR T0
    INNER JOIN WTR1 T1 ON T0.DocEntry = T1.DocEntry
    WHERE
    (T0.DocDate >= (@F_INI) AND T0.DocDate <= (@F_FIN))
    T0.Filler = ''ABRA''
    GROUP BY
    T0.DocDate, T0.U_CTROCTOEQ
    ) AS Source
    PIVOT(
    SUM(Cantidad)
    FOR Equipo IN ('+@PivotColumnHeaders+')
    ) AS PIVOTTBL'

    EXECUTE(@PivotTableSQL)
    —–