none
Inverter Coluna em Linha RRS feed

  • Pergunta

  • Boa tarde pessoal 

    Estou com o seguinte problema estou desenvolvendo uma integração entre dois sistemas onde uma empresa disponibilizou uma tabela contendo um campo do tipo texto onde o conteúdo da mesma contem dados separados por ;, até aqui tudo bem olhando na internet encontrei em um blog um exemplo simples de pegar dados separados com ; perfeito porém ai vem o meu problema:

    Exemplo:

    Suponhamos que eu tenha à seguinte string '1;2;3;4;5;6' o resultado da minha procedure será o seguinte:

    1

    2

    3

    4

    5

    6

    Gostaria de saber como eu faço para inverter a coluna e passar a mesma para linha conforme abaixo:

    1 2 3 4 5 6

    Alguem poderia me ajudar?

    terça-feira, 22 de abril de 2014 19:56

Respostas

Todas as Respostas

  • Deleted
    terça-feira, 22 de abril de 2014 20:11
  • no caso seria 57 colunas eu simplifiquei com 6 e cada uma tem uma informação diferente da outra a procedure que estou usando é a seguinte:

    ALTER proc [dbo].[MEN_SEPARADORCSV]
      @grupo varchar(8000),
      @caracter char(1) = NULL
    as
     
    begin
      if @caracter is null set @caracter = ','
     
      declare @pos int
      declare @buffer varchar(8000)
      declare @buffer2 varchar(8000)
     
      create table ret (texto varchar(8000) null, ordem int not null identity(1,1))
     
      while charindex('"', @grupo) > 0
      begin
     
        select @pos = charindex('"', @grupo)
     
        select @buffer = left(@grupo, @pos)
     
        select @grupo = substring(@grupo, @pos + 1, len(@grupo))
     
        select @pos = charindex('"', @grupo)
     
        select @buffer2 = left(@grupo, @pos)
     
        select @grupo = substring(@grupo, @pos + 1, len(@grupo))
     
        select @buffer = replace(@buffer, '"','')
             , @buffer2 = replace(@buffer2, '"', '')
     
        select @buffer2 = replace(@buffer2, @caracter, char(254))
        
        select @buffer2 = replace(@buffer2, ';', char(253))
     
        select @grupo = rtrim(@buffer) + rtrim(@buffer2) + rtrim(@grupo)
     
      end
      
      while charindex(@caracter,@grupo) > 0
      begin
        select @pos = charindex(@caracter,@grupo)
        select @buffer = left(@grupo,@pos - 1)
        select @grupo = right(@grupo,len(rtrim(@grupo)) - @pos)
        
        select @buffer = replace(@buffer, char(254), @caracter)
        select @buffer = replace(@buffer, char(253), ';')
        
        insert into ret (texto)
          select rtrim(ltrim(@buffer))
      end
     
      if @grupo <> ''
      begin
        select @buffer =replace(@grupo,@caracter,'')
     
        insert into ret (texto)
          select rtrim(ltrim(@buffer))
      end
     
      select texto from ret order by ordem
    end

    Você passa à string e o separador ele devolve no formato que mencionei, mas não sei como gerar uma tabela com esses dados informados em cada coluna.

    Pensei em uma forma alternativa que resolveria meu problema mas esta dando erro que seria no campo ordem eu tenho o número de colunas estipulado e em cada número representa uma informação das linhas existe uma forma de se realizar o seguinte procedimento:

    declare @reservado varchar (max)
    declare @emissao varchar(max)
    declare @vencimento varchar(max)
    select case ordem
              when 1 then @reservado = ''
              when 2 then @emissao = ret.texto
              when 3 then @vencimento = ret.texto
           end
    from ret

    Porém quando eu executo não dá certo tem uma forma de usar o case dessa forma pois assim eu conseguiria dar um insert na minha tabela sem criar uma estrutura enorme para inverter as linhas para colunas.

    • Editado eder.luca terça-feira, 22 de abril de 2014 20:45 Idéia nova.
    • Sugerido como Resposta Edinaldo Junior quinta-feira, 24 de abril de 2014 12:15
    terça-feira, 22 de abril de 2014 20:25
  • Deleted
    • Sugerido como Resposta Edinaldo Junior quinta-feira, 24 de abril de 2014 12:15
    terça-feira, 22 de abril de 2014 22:39
  • Utilizei o exemplo acima funcionou perfeitamente, porém passei para o meu projeto o mesmo e estou recebendo um erro que não consigo entender, abaixo segue a estrutura que montei:

    Abaixo segue o crete da minha tabela que seria equivalente a tabvalores;

    CREATE TABLE [dbo].[INTGRA_ERP](
    	[INTGR_NRO_SEQ] [int] NULL,
    	[INTGR_IND] [varchar](4) NULL,
    	[INTGR_DES_CHAVE] [varchar](max) NULL,
    	[INTGR_IND_STATUS] [varchar](1) NULL,
    	[INTGR_DTH_INCL] [datetime] NULL,
    	[INTGR_DES_DADOS_SGU] [varchar](max) NULL,
    	[INTGR_DES_DADOS_ESPCFN] [varchar](max) NULL,
    	[INTGR_DTH_INTGRA] [datetime] NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO

    Abaixo o insert da tabela acima:

    INSERT [dbo].[INTGRA_ERP] ([INTGR_NRO_SEQ], [INTGR_IND], [INTGR_DES_CHAVE], [INTGR_IND_STATUS], [INTGR_DTH_INCL], [INTGR_DES_DADOS_SGU], [INTGR_DES_DADOS_ESPCFN], [INTGR_DTH_INTGRA]) VALUES (12356, N'REC', N'7964', N'C', CAST(0x0000A30400317E50 AS DateTime), N';2;N;A;DEP;07;;341;;CARLOS H A GONCALVES;04/04/14;15/04/14;        ;00000000000273,43;00000000000,00;00000000000,00;;00000000000273,43;154;00000000000,00;00000000000,00;00000000000,00;00000000000,00;    4753002896;0;VDIE;01/01/01;00000000000273,43;00000000000000,00;00000000000000,00;00000000000000,00;00000000000000,00;00000000000000,00;;175/00007964-4;;01/02/14;00000000000000,00;PF11500796400;115;000;E;341;PF;PF0000000;00000000000000,00;00000000000000,00;00000000000000,00;00000000000185,93;00000000000087,50;00000000000000,00;2;2;0000000000007964;0115800000169300;1;2;;01/01/01;;        ;', N'PP;', CAST(0x0000A30400317E50 AS DateTime))

    Abaixo segue a tabela que recebera o resultado do pivolt:

    CREATE TABLE TITULOS (              
                           FILIAL VARCHAR(MAX),
    					   ORIGEM VARCHAR(1),
    					   TIPOPLANO VARCHAR(1),
    					   TIPOFATURA VARCHAR(1),
    					   CONTRATOLEI VARCHAR(3),
    					   SEGMENTO VARCHAR(2),
    					   RESERVADO1 VARCHAR(13),
    					   PORTADOR VARCHAR(3),
    					   RESERVADO2 VARCHAR(8),					   
    					   NOMECLIENTE VARCHAR(50),
    					   DTEMISSAO VARCHAR(8),
    					   DTVENCIMENTO VARCHAR(8),
    					   DTVENCIMENTOREAL VARCHAR(8),
    					   VLTITULO VARCHAR(17),
    					   VLIRRF VARCHAR(14),
    					   VLISS VARCHAR(14),
    					   NRBANCO VARCHAR(15),
    					   VLSALDO VARCHAR(17),
    					   AGENCIA VARCHAR(10),
    					   INSS VARCHAR(14),
    					   CSLL VARCHAR(14),
    					   COFINS VARCHAR(14),
    					   PIS VARCHAR(14),
    					   CNPJCPF VARCHAR(14),
    					   CODDISTRIATOS VARCHAR(3),
    					   DESCCONTRATO VARCHAR(4),
    					   DTCANCELAMENTO VARCHAR(8),
    					   VLPARCELA VARCHAR(17),
    					   VLDESPESAS VARCHAR(17),
    					   VLACRESCIMOS VARCHAR(17),
    					   VLAEROMEDICO VARCHAR(17),
    					   VLDESCONTOS VARCHAR(17),
    					   VLBONIFICACAO VARCHAR(17),
    					   HISTORICO VARCHAR(50),
    					   NOSSONUMERO VARCHAR(40),
    					   RESERVADO3 VARCHAR(7),
    					   COMPETENCIA VARCHAR(8),
    					   VLJUROS VARCHAR(17),
    					   REFERENCIA VARCHAR(25),
    					   CODUNIMED INTEGER,
    					   CONTRATO VARCHAR(3),
    					   SITUACAO VARCHAR(1),
    					   PORTADORBANCO VARCHAR(3),
    					   TIPOCLIENTE VARCHAR(2),
    					   CODPLANOCONTAS VARCHAR(12),
    					   ATOCOOPPRINCIPAL VARCHAR(17),
    					   ATOAUXILIAR VARCHAR(17),
    					   ATONAOCOOPERADO VARCHAR(17),
    					   ATOCOOPPRINCIPALMIC VARCHAR(17),
    					   ATOAUXILIARMICROSIGA VARCHAR(17),
    					   ATONAOCOOPERADOMICROSIGA VARCHAR(17),
    					   INDDEBCONTA VARCHAR(1),
    					   INTPCOBRANCA VARCHAR(1),
    					   NUMEROFATURA VARCHAR(16),
    					   MATRICULA VARCHAR(16),
    					   MOTIVOCANCELAMENTO VARCHAR(50),
    					   OBSERVACAOCANCELAMENTO VARCHAR(100),
    					   TEXTO1 VARCHAR(MAX),
    					   TEXTO2 VARCHAR(MAX),
    					   TEXTO3 VARCHAR(MAX),
    					   TEXTO4 VARCHAR(MAX),
    					   CODIGO INTEGER IDENTITY(1,1)
    					   )

    Logo em seguida segue à estrutra enviada pelo josé.diz já nos moldes do meu projeto:

    ;WITH
           SPLIT AS
           (
               SELECT INTGR_NRO_SEQ, 1 AS POS, 1 AS STARTPOS,  
                      CHARINDEX(';', INTGRA_ERP.INTGR_DES_DADOS_SGU + ';') - 1 AS ENDPOS
                 FROM INTGRA_ERP
                WHERE INTGRA_ERP.INTGR_IND = 'REC'
    			  AND CAST(INTGRA_ERP.INTGR_DTH_INTGRA AS DATE) BETWEEN '2014-04-01' AND '2014-04-30 '
    			  AND DATALENGTH(INTGRA_ERP.INTGR_DES_DADOS_SGU) > 0
                UNION ALL
                SELECT PRV.INTGR_NRO_SEQ, PRV.POS + 1, PRV.ENDPOS + 2,
                       CHARINDEX(';', CUR.INTGR_DES_DADOS_SGU  + ';', PRV.ENDPOS + 2) - 1
                  FROM SPLIT AS PRV
                 INNER JOIN INTGRA_ERP AS CUR ON CUR.INTGR_NRO_SEQ = PRV.INTGR_NRO_SEQ AND CHARINDEX(';', CUR.INTGR_DES_DADOS_SGU + ';', PRV.ENDPOS + 2) > 0
            ),
              ELEMENTOS AS (
              SELECT A.INTGR_NRO_SEQ, POS,
                     SUBSTRING(INTGR_DES_DADOS_SGU, STARTPOS, ENDPOS-STARTPOS+1) AS ELEMENT
                FROM INTGRA_ERP AS A
                INNER JOIN SPLIT AS S ON S.INTGR_NRO_SEQ = A.INTGR_NRO_SEQ
            )
    
    		-- INICIO DA INSERÇÃO DE DADOS NA TABELA TITULOS
            INSERT INTO TITULOS
            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],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52],
    	   	       [53],[54],[55],[56],[57],[58],[59],[60],[61]
              FROM ELEMENTOS
              PIVOT (MAX(ELEMENT) FOR POS 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],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52],
    			    					      [53],[54],[55],[56],[57],[58],[59],[60],[61])) AS PV;

    Onde a coluna INTGR_NRO_SEQ é equivalente a coluna código do exemplo enviado e o campo INTGR_DES_DADOS_SGU equivalente ao campo Valores;

    Eu criei uma procedure para executar essa rotina porém quando tento criar a mesma eu recebo logo de cara a seguinte mensagem de erro:

    Types don't match between the anchor and the recursive part in column "STARTPOS" of recursive query "SPLIT".

    Alguém poderia me ajudar com esse erro?

    domingo, 27 de abril de 2014 15:47
  • Deleted
    • Marcado como Resposta eder.luca segunda-feira, 28 de abril de 2014 12:41
    domingo, 27 de abril de 2014 22:08
  • Uma pergunta para eu listar também alem da separação do campo separado com ; o campo INTGR_NRO_SEQ tem essa possibilidade?
    terça-feira, 29 de abril de 2014 12:16
  • Deleted
    terça-feira, 29 de abril de 2014 23:34