none
Agrupar Clientes em uma unica linha, colocar como colunas os tipos de produtos RRS feed

  • Pergunta

  • Tenho um Select que mostra todas as vendas, separado por clientes, tipo de produtos e por periodo

    SELECT 
    	 ST.SALESNAME
    	 ,ST.CUSTACCOUNT
    	,ST.SALESGROUP
    	,IT.ITEMGROUPID
    	
    	,sum(case when datepart(month,ST.ShippingDateRequested) = 1 and datepart(YEAR,ST.ShippingDateRequested) = @ANO then SL.LINEAMOUNT end) as 'JAN' 
    	,sum(case when datepart(month,ST.ShippingDateRequested) = 2 and datepart(YEAR,ST.ShippingDateRequested) = @ANO then SL.LINEAMOUNT end) as 'FEV'
    	,sum(case when datepart(month,ST.ShippingDateRequested) = 3 and datepart(YEAR,ST.ShippingDateRequested) = @ANO then SL.LINEAMOUNT end) as 'MAR'
    	,sum(case when datepart(month,ST.ShippingDateRequested) = 4 and datepart(YEAR,ST.ShippingDateRequested) = @ANO then SL.LINEAMOUNT end) as 'ABR'
    	,sum(case when datepart(month,ST.ShippingDateRequested) = 5 and datepart(YEAR,ST.ShippingDateRequested) = @ANO then SL.LINEAMOUNT end) as 'MAI'
    	,sum(case when datepart(month,ST.ShippingDateRequested) = 6 and datepart(YEAR,ST.ShippingDateRequested) = @ANO then SL.LINEAMOUNT end) as 'JUN'
    	,sum(case when datepart(month,ST.ShippingDateRequested) = 7 and datepart(YEAR,ST.ShippingDateRequested) = @ANO then SL.LINEAMOUNT end) as 'JUL'      
        --,ST.ShippingDateRequested
    	, SUM(SL.LINEAMOUNT) AS TOTAL
       FROM 
    	SALESTABLE ST 
    		INNER JOIN SALESLINE SL ON ST.DATAAREAID = SL.DATAAREAID AND ST.SALESID = SL.SALESID 
    	    INNER JOIN SALESOPERATIONTYPE_BR	SPT	 ON	 ST.DATAAREAID	=	SPT.DATAAREAID AND	ST.OPERATIONID	=	SPT.OPERATIONID 
    	    INNER JOIN INVENTTABLE IT ON SL.DATAAREAID = IT.DATAAREAID AND SL.ITEMID = IT.ITEMID 
    	
    	WHERE 
    		
    		
    		AND DATEPART(YEAR, ST.SHIPPINGDATEREQUESTED) = @ANO
    		
    		
    		
    GROUP BY 
        ST.SALESNAME,
        ST.CUSTACCOUNT,
        ST.SALESGROUP,
        IT.ITEMGROUPID

    O retorno repete o cliente para cada tipo de produto, como posso transformar o tipo de produto em coluna ?

     Resultado hoje

    Resultado Atual
    
    SALESNAME| CUSTACCOUNT |SALESGROUP| ITENGROU | JAN |ETC
    Cliente1        001              vende1         roupas              100       200
    Cliente1         001             vende1         calçad           200       200
    
    
    Resutaldo Desejado
    
    SALESNAME|CUSTACCOUNT|SALESGROUP| roupas-jan |calçad-jan
    Cliente1              001                vend1            100            200


    quarta-feira, 2 de julho de 2014 17:55

Respostas

  • - Diminui a quantidade de grupo de itens para testar

     - Só não consegui colocar mais uma condição dentro do set @SQL :

     
    -- código 3
    declare @ANO int = 2014;
    declare @SQL varchar(max);
    
    -- monta títulos das colunas de valores mensais de ITEMGROUPID
    declare @Meses table (Mes char(3));
    INSERT into @Meses values 
        ('jan'),('fev'),('mar'),('abr'),('mai'),('jun'),
        ('jul'),('ago'),('set'),('out'),('nov'),('dez');
    declare @MesItem varchar(2000);
    set @MesItem= space(0);
    
    SELECT @MesItem+= '[' + I.ITEMGROUPID + '-' + M.Mes + '],'
      from @Meses as M cross join
           (SELECT lower(ITEMGROUPID) as ITEMGROUPID
              from INVENTITEMGROUP
              where DATAAREAID = 'DAT' AND ITEMGROUPID IN ('ACESS. OL', 'Alarmes', 'ALARMES OL', 'FASTCONNEC')) as I;
    
    set @MesItem= Left(@MesItem, DataLength(@MesItem) -1);
    --print @MesItem
    
    -- monta o comando SQL com o código da consulta
    set @SQL= 
    'declare @Data1 date, @Data2 date; ' +
    'set @Data1= Convert(date, ''1/1/' + Cast(@ANO as char(4)) + ''', 103); ' +
    'set @Data2= Convert(date, ''1/1/' + Cast(@ANO+1 as char(4)) + ''', 103); ' +
    'with VendaAnual as (' +
    'SELECT ' +
           'ST.SALESNAME '+
          ',ST.CUSTACCOUNT '+
          ',ST.SALESGROUP ' +
          ',(Lower(IT.ITEMGROUPID) + ''-'' + Left( Lower( DateName(month, ST.SHIPPINGDATEREQUESTED) ) ,3)) as MesItem ' +
          ',SL.LINEAMOUNT ' +
       'FROM ' +
           'SALESTABLE ST  inner join ' +
           'SALESLINE SL on ST.DATAAREAID = SL.DATAAREAID AND ST.SALESID = SL.SALESID  inner join ' +
           'SALESOPERATIONTYPE_BR SPT on ST.DATAAREAID = SPT.DATAAREAID and	ST.OPERATIONID = SPT.OPERATIONID  inner join ' +
           'INVENTTABLE IT on SL.DATAAREAID = IT.DATAAREAID AND SL.ITEMID = IT.ITEMID ' +
       'WHERE ST.SHIPPINGDATEREQUESTED >= @Data1 ' +
             'and ST.SHIPPINGDATEREQUESTED < @Data2 ' +
             --'and ST.DATAAREAID =  ' +
    ') ' +
    'SELECT SALESNAME, CUSTACCOUNT, SALESGROUP, ' + @MesItem +
      'from VendaAnual ' +
           'pivot (sum(LINEAMOUNT) for MesItem in (' + @MesItem + ')) as P';
    
    --
    --print @SQL
     execute (@SQL);

     
    • Marcado como Resposta William_droops quinta-feira, 17 de julho de 2014 12:27
    quinta-feira, 10 de julho de 2014 16:08

Todas as Respostas

  • Deleted
    quarta-feira, 2 de julho de 2014 19:19
  • A coluna ITEMGROUPID não tem um valor fixo, pode-se cadastrar uma nova categoria, este valor vem de uma outra tabela. 

    Nem com o Reporting Services eu estou conseguindo fazer. 

    William

    quinta-feira, 3 de julho de 2014 12:01
  • E aí garoto tudo bom? Segue exemplo abaixo (fiz apenas para o mês de Janeiro ok). A solução com Pivot direto ajudaria se você soubesse exatamente quais colunas tem no retorno. Como é dinâmico precisei calcular a quantidade de colunas e o retorno antes de usar pivot. Segue: 

    --query que traz os registros em colunas:
    SELECT Origem, Nome, R.JAN FROM
    (
    	select s.Origem, t.Nome,
    			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 1 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'JAN',
    			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 2 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'FEV',
    			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 3 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'MAR',
    			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 4 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'ABR',
    			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 5 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'MAI',
    			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 6 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'JUN',
    			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 7 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'JUL',
    			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 8 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'AGO',
    			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 9 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'SET',
    			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 10 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'OUT',
    			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 11 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'NOV',
    			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 12 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'DEZ'
    	from SolicitacaoAerea s inner join TipoFaturamento t on s.TipoFaturamentoId = t.TipoFaturamentoId
    	where s.Origem = 'BSB'
    	GROUP BY s.Origem, t.Nome
    ) R 
    WHERE JAN > 0
    
    Origem Nome                                                                                                 JAN
    ------ ---------------------------------------------------------------------------------------------------- -----------
    BSB    Cartão de Crédito                                                                                    2
    BSB    CARTÃO- EMPRESA1                                                                                     2
    BSB    faturado                                                                                             4
    BSB    Faturado Internacional                                                                               1
    BSB    faturado(Inativo)                                                                                    10
    BSB    Pagamento Faturado                                                                                   6
    
    --agora, retornando os registros em linha:
    DECLARE @COLS NVARCHAR(MAX),
    		  @colsPivot AS NVARCHAR(MAX),
    		  @query  AS NVARCHAR(MAX)
    
    --retorno cadeia de caracteres contendo as colunas que retornarei 
    select @colsPivot = 
      STUFF((SELECT ', IsNull(' + QUOTENAME(rtrim(X.Nome)) +', 0) as '''+ rtrim(X.Nome) + '-Jan''' 
                        from (
    						  SELECT Origem, Nome, R.JAN FROM
    									(
    										select s.Origem, t.Nome,
    												 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 1 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'JAN'
    										from SolicitacaoAerea s inner join TipoFaturamento t on s.TipoFaturamentoId = t.TipoFaturamentoId
    										where s.Origem = 'BSB'
    										GROUP BY s.Origem, t.Nome
    									) R 
    									WHERE JAN > 0
    						  ) X
                       GROUP BY X.Nome
                       ORDER BY X.Nome
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    --retorno cadeia de caracteres das colunas fisicas
    select @COLS = STUFF((SELECT distinct ', ' + QUOTENAME(Nome)
                        from (
    						  SELECT Origem, Nome, R.JAN FROM
    									(
    										select s.Origem, t.Nome,
    												 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 1 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'JAN'
    										from SolicitacaoAerea s inner join TipoFaturamento t on s.TipoFaturamentoId = t.TipoFaturamentoId
    										where s.Origem = 'BSB'
    										GROUP BY s.Origem, t.Nome
    									) R 
    									WHERE JAN > 0
    						  ) X
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    --montagem dinamica da query:
    set @query 
          = 'SELECT Origem,' + @colsPivot + ' FROM 
             (
    			SELECT Origem, Nome, R.JAN FROM
    					(
    						select s.Origem, t.Nome,
    									SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 1 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS JAN
    						from SolicitacaoAerea s inner join TipoFaturamento t on s.TipoFaturamentoId = t.TipoFaturamentoId
    						where s.Origem = ''BSB''
    						GROUP BY s.Origem, t.Nome
    					) R 
    					WHERE JAN > 0
    			) X
             pivot 
             (
                sum(JAN)
                for Nome in (' + @cols + ')
             ) p '
    
    
    execute(@query)
    
    Origem Cartão de Crédito-Jan CARTÃO- EMPRESA1-Jan faturado-Jan Faturado Internacional-Jan faturado(Inativo)-Jan Pagamento Faturado-Jan
    ------ --------------------- -------------------- ------------ -------------------------- --------------------- ----------------------
    BSB    2                     2                    4            1                          10                    6
    

    quinta-feira, 3 de julho de 2014 14:13
  • Obrigado Fabio, vou tentar se der certo marco aqui. 

    quinta-feira, 3 de julho de 2014 14:19
  • Tentei mesmo sem entender o PIVOT direito colocar no meu código e apresenta os seguintes erros 

    Msg 102, Level 15, State 1, Line 72
    Incorrect syntax near 'X'.
    Msg 102, Level 15, State 1, Line 87
    Incorrect syntax near 'JAN'.

    DECLARE @COLS NVARCHAR(MAX),
    		  @colsPivot AS NVARCHAR(MAX),
    		  @query  AS NVARCHAR(MAX)
    
    --retorno cadeia de caracteres contendo as colunas que retornarei 
    select @colsPivot = 
      STUFF((SELECT ', IsNull(' + QUOTENAME(rtrim(X.ITEMGROUPID)) +', 0) as '''+ rtrim(X.ITEMGROUPID) + '-Jan''' 
                        from (
    						  SELECT SALESNAME, ITEMGROUPID, R.JAN FROM
    									(
    										select 
    										 ST.SALESNAME
    	                                    ,ST.CUSTACCOUNT
    	                                    ,ST.SALESGROUP
    	                                    ,IT.ITEMGROUPID
    									    ,SUM (CASE WHEN DATEPART(MONTH, ST.ShippingDateRequested) = 1 AND DATEPART(YEAR, ST.ShippingDateRequested) = 2014 THEN 1 ELSE 0 END) AS 'JAN'
    										from SALESTABLE ST
    											INNER JOIN SALESLINE SL ON ST.DATAAREAID = SL.DATAAREAID AND ST.SALESID = SL.SALESID 
    	                                        INNER JOIN SALESOPERATIONTYPE_BR	SPT	 ON	 ST.DATAAREAID	=	SPT.DATAAREAID AND	ST.OPERATIONID	=	SPT.OPERATIONID 
    	                                        INNER JOIN INVENTTABLE IT ON SL.DATAAREAID = IT.DATAAREAID AND SL.ITEMID = IT.ITEMID 
    										 
    										WHERE 
    		                                 ST.DATAAREAID = 'DAT'
    		                                 AND SPT.CHECKISSALES_TAKE = 1 
    		                                 AND ST.SALESGROUP <> 'Zoope'
    		                                 AND DATEPART(YEAR, ST.SHIPPINGDATEREQUESTED) = 2014
    		
    		                                 --AND ST.CUSTACCOUNT = '001339_028'
    		
    GROUP BY 
        ST.SALESNAME,
        ST.CUSTACCOUNT,
        ST.SALESGROUP,IT.ITEMGROUPID
    									) R 
    									WHERE JAN > 0
    						  ) X
                       GROUP BY X.ITEMGROUPID
                       ORDER BY X.ITEMGROUPID
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    --retorno cadeia de caracteres das colunas fisicas
    select @COLS = STUFF((SELECT distinct ', ' + QUOTENAME(ITEMGROUPID)
                        from (
    						   select 
    										 ST.SALESNAME
    	                                    ,ST.CUSTACCOUNT
    	                                    ,ST.SALESGROUP
    	                                    ,IT.ITEMGROUPID
    									    ,SUM (CASE WHEN DATEPART(MONTH, ST.ShippingDateRequested) = 1 AND DATEPART(YEAR, ST.ShippingDateRequested) = 2014 THEN 1 ELSE 0 END) AS 'JAN'
    										from SALESTABLE ST
    											INNER JOIN SALESLINE SL ON ST.DATAAREAID = SL.DATAAREAID AND ST.SALESID = SL.SALESID 
    	                                        INNER JOIN SALESOPERATIONTYPE_BR	SPT	 ON	 ST.DATAAREAID	=	SPT.DATAAREAID AND	ST.OPERATIONID	=	SPT.OPERATIONID 
    	                                        INNER JOIN INVENTTABLE IT ON SL.DATAAREAID = IT.DATAAREAID AND SL.ITEMID = IT.ITEMID 
    										 
    										WHERE 
    		                                 ST.DATAAREAID = 'DAT'
    		                                 AND SPT.CHECKISSALES_TAKE = 1 
    		                                 AND ST.SALESGROUP <> 'Zoope'
    		                                 AND DATEPART(YEAR, ST.SHIPPINGDATEREQUESTED) = 2014
    		
    		                                
    		
                                              GROUP BY 
                                              ST.SALESNAME,
                                              ST.CUSTACCOUNT,
                                              ST.SALESGROUP,
                                              IT.ITEMGROUPID
    									) R 
    									WHERE JAN > 0
    						  ) X
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    --montagem dinamica da query:
    set @query 
          = 'SELECT ST.SALESNAME, ST.CUSTACCOUNT, ST.SALESGROUP' + @colsPivot + ' FROM 
             (
    			SELECT Origem, Nome, R.JAN FROM
    					(
    						 ST.SALESNAME
    	                                    ,ST.CUSTACCOUNT
    	                                    ,ST.SALESGROUP
    	                                    ,IT.ITEMGROUPID
    									    ,SUM (CASE WHEN DATEPART(MONTH, ST.ShippingDateRequested) = 1 AND DATEPART(YEAR, ST.ShippingDateRequested) = 2014 THEN 1 ELSE 0 END) AS 'JAN'
    										from SALESTABLE ST
    											INNER JOIN SALESLINE SL ON ST.DATAAREAID = SL.DATAAREAID AND ST.SALESID = SL.SALESID 
    	                                        INNER JOIN SALESOPERATIONTYPE_BR	SPT	 ON	 ST.DATAAREAID	=	SPT.DATAAREAID AND	ST.OPERATIONID	=	SPT.OPERATIONID 
    	                                        INNER JOIN INVENTTABLE IT ON SL.DATAAREAID = IT.DATAAREAID AND SL.ITEMID = IT.ITEMID 
    										 
    										WHERE 
    		                                 ST.DATAAREAID = 'DAT'
    		                                 AND SPT.CHECKISSALES_TAKE = 1 
    		                                 AND ST.SALESGROUP <> 'Zoope'
    		                                 AND DATEPART(YEAR, ST.SHIPPINGDATEREQUESTED) = 2014
    		
    		                                
    		
                                              GROUP BY 
                                              ST.SALESNAME,
                                              ST.CUSTACCOUNT,
                                              ST.SALESGROUP,
                                              IT.ITEMGROUPID
    					) R 
    					WHERE JAN > 0
    			) X
             pivot 
             (
                sum(JAN)
                for Nome in (' + @cols + ')
             ) p '
    
    
    execute(@query)

    quinta-feira, 3 de julho de 2014 17:06
  • Bom dia Fabio; 

    Caso eu saiba a quantidade de colunas, ou seja, estou pensando em deixar Fixo, quando aparecer um grupo novo eu acrescento manualmente. 

    Ou então, dando um Select na tabela de grupos, como Ficaria ? 

    Assim, o código ficou grande e complicado para eu entender o pivot. 

    Obrigado 

    sexta-feira, 4 de julho de 2014 12:34
  • Deleted
    segunda-feira, 7 de julho de 2014 22:13
  • Olá José, agradeço a atenção. 

    O campo esta como datetime 

    O código retorna sim, uma linha para cada cliente- grupo_item - mes_ano

    AIRTON  SILVA - ME 0000198 OLIVEIRA Modulos 5 152.341.000.000.000
    ALDO   - ME C003784 Kstc PROD KST 5 1.989.000.000.000.000
    AJAPAN COM.  LTDA C002818 NISS Acessorios 1 1.309.580.000.000.000
    AJAPAN COM. LTDA C002818 NISS Alarmes 1 469.860.000.000.000
    AULOS LTDA 002590 NISS Acessorios 1 4.970.250.000.000.000
    ACULOS LTDA 0025908 NISS Acessorios 4 5.997.700.000.000.000
    ASMOTIVOS LTDA - ME C003795 RR Alarmes 5 1.951.385.800.000.000













    terça-feira, 8 de julho de 2014 12:26
  • Deleted
    terça-feira, 8 de julho de 2014 18:18
  • Bom dia Jose Diz, ao colar o resultado, ficaram grandes as colunas, então essa "sujeira" foi feita por mim na hora de responder no fórum, alguns espaços e algumas letras me passaram desapercebidos.

    Com relação aos valores eu formato na hora de exibir 

    Seria apenas 152,34 . 

    Obrigado 

    quinta-feira, 10 de julho de 2014 10:37
  • Deleted
    quinta-feira, 10 de julho de 2014 12:50
  • Agradeço a atenção, ao executar o comando, o retorno foi.


    (12 row(s) affected)
    [Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Alarmes-jan],[Alarmes-jan],[Alarmes-jan],[Alarmes-jan],[Alarmes-jan],[Alarmes-jan],[Alarmes-ja

    quinta-feira, 10 de julho de 2014 13:44
  • Deleted
    quinta-feira, 10 de julho de 2014 13:48
  • Se me permite, alterei para buscar na tabela de grupos de itens 

    -- código 2 v3 ( Adicionei Inventitemgroup )
    -- monta títulos das colunas de valores mensais de ITEMGROUPID
    declare @Meses table (Mes char(3));
    INSERT into @Meses values 
        ('jan'),('fev'),('mar'),('abr'),('mai'),('jun'),
        ('jul'),('ago'),('set'),('out'),('nov'),('dez');
    declare @MesItem varchar(2000);
    set @MesItem= space(0);
    SELECT @MesItem+= '[' + I.ITEMGROUPID + '-' + M.Mes + '],'
      from @Meses as M cross join
           (SELECT distinct ITEMGROUPID from INVENTITEMGROUP) as I;
    
    set @MesItem= Left(@MesItem, DataLength(@MesItem) -1);
    print @MesItem;

    (12 row(s) affected)
    [Acessorios-jan],[AGUA-jan],[Alarmes-jan],[ASSESSORIA-jan],[ASSISTENCI-jan],[ATIVO-jan],[ATIVO 01-jan],[ATIVO 02-jan],[ATIVO 03-jan],[ATIVO 04-jan],[ATIVO 05-jan]

    quinta-feira, 10 de julho de 2014 13:57
  • Deleted
    quinta-feira, 10 de julho de 2014 14:04
  • Perfeito, uma linha para cada grupo, tem grupos que não são de venda, mas depois eu tiro da relação. 
    quinta-feira, 10 de julho de 2014 14:06
  • Deleted
    quinta-feira, 10 de julho de 2014 14:53
  • Deleted
    quinta-feira, 10 de julho de 2014 15:05
  • Alterei apenas um ponto pois deu erro de sintase 
    -- código 2 v5
    -- monta títulos das colunas de valores mensais de ITEMGROUPID
    declare @Meses table (Mes char(3));
    INSERT into @Meses values 
        ('jan'),('fev'),('mar'),('abr'),('mai'),('jun'),
        ('jul'),('ago'),('set'),('out'),('nov'),('dez');
    declare @MesItem varchar(2000);
    set @MesItem= space(0);
    SELECT @MesItem+= '[' + I.ITEMGROUPID + '-' + M.Mes + '],'
      from @Meses as M cross join
           (SELECT lower(ITEMGROUPID) as ITEMGROUPID
              from INVENTITEMGROUP
              where DATAAREAID = 'dat') as I;

    quinta-feira, 10 de julho de 2014 15:10
  • - Diminui a quantidade de grupo de itens para testar

     - Só não consegui colocar mais uma condição dentro do set @SQL :

     
    -- código 3
    declare @ANO int = 2014;
    declare @SQL varchar(max);
    
    -- monta títulos das colunas de valores mensais de ITEMGROUPID
    declare @Meses table (Mes char(3));
    INSERT into @Meses values 
        ('jan'),('fev'),('mar'),('abr'),('mai'),('jun'),
        ('jul'),('ago'),('set'),('out'),('nov'),('dez');
    declare @MesItem varchar(2000);
    set @MesItem= space(0);
    
    SELECT @MesItem+= '[' + I.ITEMGROUPID + '-' + M.Mes + '],'
      from @Meses as M cross join
           (SELECT lower(ITEMGROUPID) as ITEMGROUPID
              from INVENTITEMGROUP
              where DATAAREAID = 'DAT' AND ITEMGROUPID IN ('ACESS. OL', 'Alarmes', 'ALARMES OL', 'FASTCONNEC')) as I;
    
    set @MesItem= Left(@MesItem, DataLength(@MesItem) -1);
    --print @MesItem
    
    -- monta o comando SQL com o código da consulta
    set @SQL= 
    'declare @Data1 date, @Data2 date; ' +
    'set @Data1= Convert(date, ''1/1/' + Cast(@ANO as char(4)) + ''', 103); ' +
    'set @Data2= Convert(date, ''1/1/' + Cast(@ANO+1 as char(4)) + ''', 103); ' +
    'with VendaAnual as (' +
    'SELECT ' +
           'ST.SALESNAME '+
          ',ST.CUSTACCOUNT '+
          ',ST.SALESGROUP ' +
          ',(Lower(IT.ITEMGROUPID) + ''-'' + Left( Lower( DateName(month, ST.SHIPPINGDATEREQUESTED) ) ,3)) as MesItem ' +
          ',SL.LINEAMOUNT ' +
       'FROM ' +
           'SALESTABLE ST  inner join ' +
           'SALESLINE SL on ST.DATAAREAID = SL.DATAAREAID AND ST.SALESID = SL.SALESID  inner join ' +
           'SALESOPERATIONTYPE_BR SPT on ST.DATAAREAID = SPT.DATAAREAID and	ST.OPERATIONID = SPT.OPERATIONID  inner join ' +
           'INVENTTABLE IT on SL.DATAAREAID = IT.DATAAREAID AND SL.ITEMID = IT.ITEMID ' +
       'WHERE ST.SHIPPINGDATEREQUESTED >= @Data1 ' +
             'and ST.SHIPPINGDATEREQUESTED < @Data2 ' +
             --'and ST.DATAAREAID =  ' +
    ') ' +
    'SELECT SALESNAME, CUSTACCOUNT, SALESGROUP, ' + @MesItem +
      'from VendaAnual ' +
           'pivot (sum(LINEAMOUNT) for MesItem in (' + @MesItem + ')) as P';
    
    --
    --print @SQL
     execute (@SQL);

     
    • Marcado como Resposta William_droops quinta-feira, 17 de julho de 2014 12:27
    quinta-feira, 10 de julho de 2014 16:08
  • Deleted
    quinta-feira, 10 de julho de 2014 20:05
  • Jose Diz, na base de teste atendeu, acredito que é isto mesmo a solução que preciso, vou testar de uma maneira completa, mas já agradeço a atenção e interesse. 

    Atendendo e não tendo mais duvidas finalizo o tópico. 

    quinta-feira, 10 de julho de 2014 20:21
  • Deleted
    quinta-feira, 10 de julho de 2014 20:26
  • Teria que montar o agrupamento por colunas no reporting Service, correto ?
    quinta-feira, 10 de julho de 2014 20:28
  • Boa tarde Jose Diz, tudo bem ? 

    A lógica funcionou perfeitamente. 

    Com poucos produtos o resultado é satisfatório, porém quando coloco para buscar na tabela de Grupo de Itens, acredito que deve ter um Limite e não monta o select completo. 
    Ao printar no final ele corta as opções.

    declare @Data1 date, @Data2 date; set @Data1= Convert(date, '1/1/2014', 103); set @Data2= Convert(date, '1/1/2015', 103); with VendaAnual as (SELECT ST.SALESNAME ,ST.CUSTACCOUNT ,ST.SALESGROUP ,(Lower(IT.ITEMGROUPID) + '-' + Left( Lower( DateName(month, ST.SHIPPINGDATEREQUESTED) ) ,3)) as MesItem ,SL.LINEAMOUNT FROM SALESTABLE ST  inner join SALESLINE SL on ST.DATAAREAID = SL.DATAAREAID AND ST.SALESID = SL.SALESID  inner join SALESOPERATIONTYPE_BR SPT on ST.DATAAREAID = SPT.DATAAREAID and	ST.OPERATIONID = SPT.OPERATIONID  inner join INVENTTABLE IT on SL.DATAAREAID = IT.DATAAREAID AND SL.ITEMID = IT.ITEMID WHERE ST.SHIPPINGDATEREQUESTED >= @Data1 and ST.SHIPPINGDATEREQUESTED < @Data2 and ST.DATAAREAID = 'dat' and IT.ITEMGROUPID not in  ('AGUA', 'ASSESSORIA', 'ASSISTENCI','ATIVO','BENS DE PE','BRINDES E', 'COMBUSTIVE','COMUNICACA','CONFRATERN', 'DESMP','DESP KASAT','DESPA','DESPCOMISS','Despesas','ELERGIA EL','EST MANUTE','ESTACIONAM','FARMACIA', 'FERRAMENTA', 'FRETE','GRAFICA', 'HIGIENE E', 'IMPRESSOS', 'INDUSTRIAL', 'INTERNET','LANCHES E', 'MANUT. E C', 'MANUT-MAQ','MAOOBRA', 'MAT AUX CO', 'MAT.TERCEI','MP','MP KASATEC', 'MP OLIMPUS','NFC','OUT ALUGUE', 'POSTAIS', 'PROMOÇÕES','PROPAGANDA','SERV PREST', 'SERVIÇO', 'SERVS.P.JU','SUCATA','TREINAMENT','VALE REFEI','VALE TRANS', 'VEICULO', 'VIAGENS E') ) SELECT SALESNAME, CUSTACCOUNT, SALESGROUP, [ACESSORIOS OLIMPUS-JAN],[ACESSÓRIOS-JAN],[ALARMES MOTO OLIMPUS-JAN],[ALARMES-JAN],[ALARMES OLIMPUS-JAN],[ANTENAS OLIMPUS-JAN],[COMFORT-JAN],[DSH OLIMPUS-JAN],[MÓDULOS FAST CONNECTION-JAN],[MÓDULOS-JAN],[MÓDULOS KT-JAN],[OUTROS OLIMPUS-JAN],[PRODUTO ACABADO KASATEC-JAN],[REVENDA KASATEC-JAN],[SAFELIFT-JAN],[SUBPRODUTO-JAN],[TRAVAS-JAN],[ACESSORIOS OLIMPUS-FEV],[ACESSÓRIOS-FEV],[ALARMES MOTO OLIMPUS-FEV],[ALARMES-FEV],[ALARMES OLIMPUS-FEV],[ANTENAS OLIMPUS-FEV],[COMFORT-FEV],[DSH OLIMPUS-FEV],[MÓDULOS FAST CONNECTION-FEV],[MÓDULOS-FEV],[MÓDULOS KT-FEV],[OUTROS OLIMPUS-FEV],[PRODUTO ACABADO KASATEC-FEV],[REVENDA KASATEC-FEV],[SAFELIFT-FEV],[SUBPRODUTO-FEV],[TRAVAS-FEV],[ACESSORIOS OLIMPUS-MAR],[ACESSÓRIOS-MAR],[ALARMES MOTO OLIMPUS-MAR],[ALARMES-MAR],[ALARMES OLIMPUS-MAR],[ANTENAS OLIMPUS-MAR],[COMFORT-MAR],[DSH OLIMPUS-MAR],[MÓDULOS FAST CONNECTION-MAR],[MÓDULOS-MAR],[MÓDULOS KT-MAR],[OUTROS OLIMPUS-MAR],[PRODUTO ACABADO KASATEC-MAR],[REVENDA KASATEC-MAR],[SAFELIFT-MAR],[SUBPRODUTO-MAR],[TRAVAS-MAR],[ACESSORIOS OLIMPUS-ABR],[ACESSÓRIOS-ABR],[ALARMES MOTO OLIMPUS-ABR],[ALARMES-ABR],[ALARMES OLIMPUS-ABR],[ANTENAS OLIMPUS-ABR],[COMFORT-ABR],[DSH OLIMPUS-ABR],[MÓDULOS FAST CONNECTION-ABR],[MÓDULOS-ABR],[MÓDULOS KT-ABR],[OUTROS OLIMPUS-ABR],[PRODUTO ACABADO KASATEC-ABR],[REVENDA KASATEC-ABR],[SAFELIFT-ABR],[SUBPRODUTO-ABR],[TRAVAS-ABR],[ACESSORIOS OLIMPUS-MAI],[ACESSÓRIOS-MAI],[ALARMES MOTO OLIMPUS-MAI],[ALARMES-MAI],[ALARMES OLIMPUS-MAI],[ANTENAS OLIMPUS-MAI],[COMFORT-MAI],[DSH OLIMPUS-MAI],[MÓDULOS FAST CONNECTION-MAI],[MÓDULOS-MAI],[MÓDULOS KT-MAI],[OUTROS OLIMPUS-MAI],[PRODUTO ACABADO KASATEC-MAI],[REVENDA KASATEC-MAI],[SAFELIFT-MAI],[SUBPRODUTO-MAI],[TRAVAS-MAI],[ACESSORIOS OLIMPUS-JUN],[ACESSÓRIOS-JUN],[ALARMES MOTO OLIMPUS-JUN],[ALARMES-JUN],[ALARMES OLIMPUS-JUN],[ANTENAS OLIMPUS-JUN],[COMFORT-JUN],[DSH OLIMPUS-JUN],[MÓDULOS FAST CONNECTION-JUN],[MÓDULOS-JUN],[MÓDULOS KT-JUN],[OUTROS OLIMPUS-JUN],[PRODUTO ACABADO KASATEC-JUN],[REVENDA KASATEC-JUN],[SAFELIFT-JUN],[from VendaAnual pivot (sum(LINEAMOUNT) for MesItem in ([ACESSORIOS OLIMPUS-JAN],[ACESSÓRIOS-JAN],[ALARMES MOTO OLIMPUS-JAN],[ALARMES-JAN],[ALARMES OLIMPUS-JAN],[ANTENAS OLIMPUS-JAN],[COMFORT-JAN],[DSH OLIMPUS-JAN],[MÓDULOS FAST CONNECTION-JAN],[MÓDULOS-JAN],[MÓDULOS KT-JAN],[OUTROS OLIMPUS-JAN],[PRODUTO ACABADO KASATEC-JAN],[REVENDA KASATEC-JAN],[SAFELIFT-JAN],[SUBPRODUTO-JAN],[TRAVAS-JAN],[ACESSORIOS OLIMPUS-FEV],[ACESSÓRIOS-FEV],[ALARMES MOTO OLIMPUS-FEV],[ALARMES-FEV],[ALARMES OLIMPUS-FEV],[ANTENAS OLIMPUS-FEV],[COMFORT-FEV],[DSH OLIMPUS-FEV],[MÓDULOS FAST CONNECTION-FEV],[MÓDULOS-FEV],[MÓDULOS KT-FEV],[OUTROS OLIMPUS-FEV],[PRODUTO ACABADO KASATEC-FEV],[REVENDA KASATEC-FEV],[SAFELIFT-FEV],[SUBPRODUTO-FEV],[TRAVAS-FEV],[ACESSORIOS OLIMPUS-MAR],[ACESSÓRIOS-MAR],[ALARMES MOTO OLIMPUS-MAR],[ALARMES-MAR],[ALARMES OLIMPUS-MAR],[ANTENAS OLIMPUS-MAR],[COMFORT-MAR],[DSH OLIMPUS-MAR],[MÓDULOS FAST CONNECTION-MAR],[MÓDULOS-MAR],[MÓDULOS KT-MAR],[OUTROS OLIMPUS-MAR],[PRODUTO ACABADO KASATEC-MAR],[REVENDA KASATEC-MAR],[SAFELIFT-MAR],[SUBPRODUTO-MAR],[TRAVAS-MAR],[ACESSORIOS OLIMPUS-ABR],[ACESSÓRIOS-ABR],[ALARMES MOTO OLIMPUS-ABR],[ALARMES-ABR],[ALARMES OLIMPUS-ABR],[ANTENAS OLIMPUS-ABR],[COMFORT-ABR],[DSH OLIMPUS-ABR],[MÓDULOS FAST CONNECTION-ABR],[MÓDULOS-ABR],[MÓDULOS KT-ABR],[OUTROS OLIMPUS-ABR],[PRODUTO ACABADO KASATEC-ABR],[REVENDA KASATEC-ABR],[SAFELIFT-ABR],[SUBPRODUTO-ABR],[TRAVAS-ABR],[ACESSORIOS OLIMPUS-MAI],[ACESSÓRIOS-MAI],[ALARMES MOTO OLIMPUS-MAI],[ALARMES-MAI],[ALARMES OLIMPUS-MAI],[ANTENAS OLIMPUS-MAI],[COMFORT-MAI],[DSH OLIMPUS-MAI],[MÓDULOS FAST CONNECTION-MAI],[MÓDULOS-MAI],[MÓDULOS KT-MAI],[OUTROS OLIMPUS-MAI],[PRODUTO ACABADO KASATEC-MAI],[REVENDA KASATEC-MAI],[SAFELIFT-MAI],[SUBPRODUTO-MAI],[TRAVAS-MAI],[ACESSORIOS OLIMPUS-JUN],[ACESSÓRIOS-JUN],[ALARMES MOTO OLIMPUS-JUN],[ALARMES-JUN],[ALARMES OLIMPUS-JUN],[ANTENAS OLIMPUS-JUN],[COMFORT-JUN],[DSH OLIMPUS-JUN],[MÓDULOS FAST CONNECTION-JUN],[MÓDULOS-JUN],[MÓDULOS KT-JUN],[OUTROS OLIMPUS-JUN],[PRODUTO ACABADO KASATEC-JUN],[REVENDA KASATEC-JUN],[SAFELIFT-JUN],[)) as P
    Msg 105, Level 15, State 1, Line 1
    Unclosed quotation mark after the character string ')) as P'.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ')) as P'


    Teria como eu fazer de uma maneira dinâmica de 5 meses, 2 meses anterior , mes atual, 2 meses posterior. 

    quarta-feira, 16 de julho de 2014 18:03
  • Deleted
    quarta-feira, 16 de julho de 2014 19:55
  • Perfeito, somente com Max, funcionou. 

    Obrigado

    quarta-feira, 16 de julho de 2014 20:04