none
Pivot table. RRS feed

  • Pergunta

  • Boa tarde!

    como faço um pivot table para essa query, mas com o seguinte, os dias do mês serão as colunas e, lembrando que, alguns meses tem 30 dias, outros tem 31, outro 28 e, é incremental, getdate()-1 (d-1), tipo, hj é dia 14, consulta do dia 1 até o 13...mas mes passado consulta os 31 dias, e assim por diante...se alguém puder me ajudar, não sou profundo conhecedor de pivot table, por favor..

    SELECT DIA, MES, CAMPANHA, SUM(VENDA) AS Venda, SUM(Pedidos) as Pedidos, SUM(aponta) as Aponta from #v_pedidos_contrato_mapa_B2Bgroup by MES,DIA,  CAMPANHAORDER BY DIA ASC


    segunda-feira, 14 de abril de 2014 14:53

Respostas

Todas as Respostas

  • Boa tarde!

    como faço um pivot table para essa query, mas com o seguinte, os dias do mês serão as colunas e, lembrando que, alguns meses tem 30 dias, outros tem 31, outro 28 e, é incremental, getdate()-1 (d-1), tipo, hj é dia 14, consulta do dia 1 até o 13...mas mes passado consulta os 31 dias, e assim por diante...se alguém puder me ajudar, não sou profundo conhecedor de pivot table, por favor..

    SELECT DIA, MES, CAMPANHA, SUM(VENDA) AS Venda, SUM(Pedidos) as Pedidos, SUM(aponta) as Aponta from #v_pedidos_contrato_mapa_B2Bgroup by MES,DIA,  CAMPANHAORDER BY DIA ASC


    eu imaginei que fosse ter que criar [31] campos de DIA, mas não entendi..
    segunda-feira, 14 de abril de 2014 15:27
  • uma ajuda pessoal, por favor
    segunda-feira, 14 de abril de 2014 16:23
  • Cheguei nesse código, mas dá erro:

    SELECT [DIA], [CAMPANHA], [SUM(VENDA) AS Venda], [SUM(Pedidos) as Pedidos], [SUM(aponta) as Aponta] from #v_pedidos_contrato_mapa_B2BPivot ([DIA] FOR [CAMPANHA] IN([CAMPANHA])) AS Pgroup by DIA,  CAMPANHAORDER BY DIA ASC

    eu precisava tipo assim:

              1 2 3 4 5 6 ...até 28 ou 30 ou 31

    Campanha, soma vendas, soma pedidos, soma aponta para cada dia...

    alguem tem alguma ideia para ajudar? por favor

    segunda-feira, 14 de abril de 2014 18:41
  • Deleted
    • Sugerido como Resposta Giovani Cr terça-feira, 15 de abril de 2014 15:12
    • Marcado como Resposta Durval RamosModerator sábado, 13 de setembro de 2014 01:56
    segunda-feira, 14 de abril de 2014 22:33
  •    
    declare @Competencia varchar(6) = '201404'declare @values as nvarchardeclare @query as nvarcharSELECT	 CASE WHEN FLVENDACANC = 'S' THEN count(pedido*-1) ELSE count(pedido) END as Pedidos	,CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END AS Venda	,DAY(DTPEDIDO)  DIA	,MES	,CAMPANHA 	,CASE WHEN FLVENDACANC = 'S' 	 AND MES = '01' THEN SUM(vlvenda*-1) / day(GETDATE()-1) * (31 - day(GETDATE()-1))	 ELSE SUM(vlvenda)/day(GETDATE()-1) * (31 - day(GETDATE()-1)) END 	 + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END	 +(CASE WHEN FLVENDACANC = 'S' 	 AND MES = '02' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END			 /day(GETDATE()-1))* (28 - day(GETDATE()-1))  			 + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END 			 + CASE WHEN FLVENDACANC = 'S' and MES = '03' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END	 /day(GETDATE()-1) * (31 - day(GETDATE()-1)) + + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END 			 + CASE WHEN FLVENDACANC = 'S' and MES = '04' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END/day(GETDATE()-1) * 	 (30 - day(GETDATE()-1)) + + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END 			 + CASE WHEN FLVENDACANC = 'S' and MES = '05' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END/day(GETDATE()-1) * 			 (31 - day(GETDATE()-1)) + + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END 			 + CASE WHEN FLVENDACANC = 'S' and MES = '06' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END/day(GETDATE()-1) * 			 (30 - day(GETDATE()-1)) + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END 			 + CASE WHEN FLVENDACANC = 'S' and MES = '07' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END/day(GETDATE()-1) *			 (31 - day(GETDATE()-1)) + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END + + CASE WHEN FLVENDACANC = 'S' 	 AND MES = '08' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END/day(GETDATE()-1) *			 (31 - day(GETDATE()-1)) + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END + + CASE WHEN FLVENDACANC = 'S' 	 AND MES = '09' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END/day(GETDATE()-1) *	 (30 - day(GETDATE()-1)) + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END + + CASE WHEN FLVENDACANC = 'S' 	 AND MES = '10' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END/day(GETDATE()-1) *		 (31 - day(GETDATE()-1)) + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END +	+ CASE WHEN FLVENDACANC = 'S' 	 AND MES = '11' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END/day(GETDATE()-1) *			 (30 - day(GETDATE()-1)) + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END + + CASE WHEN FLVENDACANC = 'S' 	 AND MES = '12' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END/day(GETDATE()-1) *			 (31 - day(GETDATE()-1)) + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END AS Aponta 	into #v_pedidos_contrato_mapa_B2B	 from dwMagazineLuiza.dbo.v_pedidos_contrato_mapa_B2B (nolock) 	where CANAL = 'PROGRAMAS' AND (COMPETENCIA = @Competencia) 	AND DAY(DTPEDIDO) <= DAY(GETDATE() - 1)group by FLVENDACANC, campanha, DAY(DTPEDIDO), MES, vlvenda, PEDIDO/*Select 	[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23]	,[24],[25],[26],[27],[28],[29],[30],[31]from #v_pedidos_contrato_mapa_B2B PIV --where DIA <= DIA(GETDATE() - 1)	pivot (sum(venda) for CAMPANHA in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23]	,[24],[25],[26],[27],[28],[29],[30],[31])) porder by 1*/--declare @values as varchar(max)SELECT  @values = STUFF(( SELECT distinct '],[' + DIAFROM    #v_pedidos_contrato_mapa_B2BORDER BY '],[' + DIAFOR XML PATH('')), 1, 2, '') + ']'set @query = 'Select CAMPANHA, |@from(campanha , DIA, MES, SUM(vlvenda) VENDA, SUM(PEDIDO) PEDIDO, SUM(APONTA) APONTA from #v_pedidos_contrato_mapa_B2B) PTPIVOT(SUM(vlvenda) for DIA in ( |@ )) pvt'set @query = REPLACE(@query,'|@',@values)exec(@query)--- Separando os valores: Passo 1SELECT  @values = STUFF(( SELECT DISTINCT'],[' + DIAFROM    #v_pedidos_contrato_mapa_B2BORDER BY '],[' + DIAFOR XML PATH('')), 1, 2, '') + ']'--- Esqueleto da query com os marcadores: Passo 2set @query = 'Select Marca, |@from(campanha , DIA, MES, SUM(vlvenda) VENDA, SUM(PEDIDO) PEDIDO, SUM(APONTA) APONTA from #v_pedidos_contrato_mapa_B2B) PTPIVOT( SUM(vlvenda) for DIA in ( |@ )) pvt'set @query = REPLACE(@query,'|@',@values)--- Executando a queryexec(@query)drop table #v_pedidos_contrato_mapa_B2B--select * from #v_pedidos_contrato_mapa_B2B

    Cheguei nesse ponto..mas o erro: 

    (32966 row(s) affected)
    Msg 2812, Level 16, State 62, Line 1
    Could not find stored procedure 'S'.
    Msg 2812, Level 16, State 62, Line 1
    Could not find stored procedure 'S'.
    terça-feira, 15 de abril de 2014 19:09
  •    
    declare @Competencia varchar(6) = '201404'declare @values as nvarchardeclare @query as nvarcharSELECT	 CASE WHEN FLVENDACANC = 'S' THEN count(pedido*-1) ELSE count(pedido) END as Pedidos	,CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END AS Venda	,DAY(DTPEDIDO)  DIA	,MES	,CAMPANHA 	,CASE WHEN FLVENDACANC = 'S' 	 AND MES = '01' THEN SUM(vlvenda*-1) / day(GETDATE()-1) * (31 - day(GETDATE()-1))	 ELSE SUM(vlvenda)/day(GETDATE()-1) * (31 - day(GETDATE()-1)) END 	 + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END	 +(CASE WHEN FLVENDACANC = 'S' 	 AND MES = '02' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END			 /day(GETDATE()-1))* (28 - day(GETDATE()-1))  			 + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END 			 + CASE WHEN FLVENDACANC = 'S' and MES = '03' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END	 /day(GETDATE()-1) * (31 - day(GETDATE()-1)) + + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END 			 + CASE WHEN FLVENDACANC = 'S' and MES = '04' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END/day(GETDATE()-1) * 	 (30 - day(GETDATE()-1)) + + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END 			 + CASE WHEN FLVENDACANC = 'S' and MES = '05' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END/day(GETDATE()-1) * 			 (31 - day(GETDATE()-1)) + + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END 			 + CASE WHEN FLVENDACANC = 'S' and MES = '06' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END/day(GETDATE()-1) * 			 (30 - day(GETDATE()-1)) + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END 			 + CASE WHEN FLVENDACANC = 'S' and MES = '07' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END/day(GETDATE()-1) *			 (31 - day(GETDATE()-1)) + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END + + CASE WHEN FLVENDACANC = 'S' 	 AND MES = '08' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END/day(GETDATE()-1) *			 (31 - day(GETDATE()-1)) + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END + + CASE WHEN FLVENDACANC = 'S' 	 AND MES = '09' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END/day(GETDATE()-1) *	 (30 - day(GETDATE()-1)) + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END + + CASE WHEN FLVENDACANC = 'S' 	 AND MES = '10' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END/day(GETDATE()-1) *		 (31 - day(GETDATE()-1)) + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END +	+ CASE WHEN FLVENDACANC = 'S' 	 AND MES = '11' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END/day(GETDATE()-1) *			 (30 - day(GETDATE()-1)) + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END + + CASE WHEN FLVENDACANC = 'S' 	 AND MES = '12' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END/day(GETDATE()-1) *			 (31 - day(GETDATE()-1)) + CASE WHEN FLVENDACANC = 'S' THEN SUM(vlvenda*-1) ELSE SUM(vlvenda) END AS Aponta 	into #v_pedidos_contrato_mapa_B2B	 from dwMagazineLuiza.dbo.v_pedidos_contrato_mapa_B2B (nolock) 	where CANAL = 'PROGRAMAS' AND (COMPETENCIA = @Competencia) 	AND DAY(DTPEDIDO) <= DAY(GETDATE() - 1)group by FLVENDACANC, campanha, DAY(DTPEDIDO), MES, vlvenda, PEDIDO/*Select 	[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23]	,[24],[25],[26],[27],[28],[29],[30],[31]from #v_pedidos_contrato_mapa_B2B PIV --where DIA <= DIA(GETDATE() - 1)	pivot (sum(venda) for CAMPANHA in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23]	,[24],[25],[26],[27],[28],[29],[30],[31])) porder by 1*/--declare @values as varchar(max)SELECT  @values = STUFF(( SELECT distinct '],[' + DIAFROM    #v_pedidos_contrato_mapa_B2BORDER BY '],[' + DIAFOR XML PATH('')), 1, 2, '') + ']'set @query = 'Select CAMPANHA, |@from(campanha , DIA, MES, SUM(vlvenda) VENDA, SUM(PEDIDO) PEDIDO, SUM(APONTA) APONTA from #v_pedidos_contrato_mapa_B2B) PTPIVOT(SUM(vlvenda) for DIA in ( |@ )) pvt'set @query = REPLACE(@query,'|@',@values)exec(@query)--- Separando os valores: Passo 1SELECT  @values = STUFF(( SELECT DISTINCT'],[' + DIAFROM    #v_pedidos_contrato_mapa_B2BORDER BY '],[' + DIAFOR XML PATH('')), 1, 2, '') + ']'--- Esqueleto da query com os marcadores: Passo 2set @query = 'Select Marca, |@from(campanha , DIA, MES, SUM(vlvenda) VENDA, SUM(PEDIDO) PEDIDO, SUM(APONTA) APONTA from #v_pedidos_contrato_mapa_B2B) PTPIVOT( SUM(vlvenda) for DIA in ( |@ )) pvt'set @query = REPLACE(@query,'|@',@values)--- Executando a queryexec(@query)drop table #v_pedidos_contrato_mapa_B2B--select * from #v_pedidos_contrato_mapa_B2B

    Cheguei nesse ponto..mas o erro: 

    (32966 row(s) affected)
    Msg 2812, Level 16, State 62, Line 1
    Could not find stored procedure 'S'.
    Msg 2812, Level 16, State 62, Line 1
    Could not find stored procedure 'S'.

    tirei um convert para varchar q tinha no DIA que é int e o erro passa ser esse:

    (32966 row(s) affected)
    Msg 245, Level 16, State 1, Line 56
    Conversion failed when converting the varchar value '],[' to data type int.

    terça-feira, 15 de abril de 2014 19:12
  • Poxa pessoal, ng pode dar uma ajuda? nem um link? não acho nada de pivot com sum e nem quando usarei varias colunas como linhas e uma linha como coluna...
    quarta-feira, 16 de abril de 2014 12:07