none
Como extrair apenas os valores que estão entre aspas no mesmo campo depois gravá-los em outra tabela separados por cada campo RRS feed

  • Pergunta

  • Boa tarde, Pessoal"

    Sou iniciante em SQL, em uma tabela do nosso BD tempos um campo que possui o registro com as informações do paciente concatenado com uma serie de valores que não precisamos.

    Como eu faço, para montar uma select para extrair desse registro apenas o que está entre aspas?

    O meu registro está assim:

    a:28:{i:18;s:13:"Sérgio silva";i:19;s:11:"1199900001";i:20;s:0:"n/i";i:21;s:0:"n/i";i:23;s:15:"Amil One Health";i:24;s:0:"n/i";i:27;s:0:"n/i";i:31;s:1:"2";i:32;s:0:"n/i";i:33;s:1:"2";i:34;s:0:;}

    Eu preciso dele assim:

    "Sérgio silva";"1199900001";"n/i";"n/i";"Amil One Health";"n/i";"n/i";"2";"n/i";"2";

    Observação, esses monte de valores a:28: ...etc... eles mudam, não são valores fixos, se fossem até usaria o replace, mas são vários valores.

    Depois que eu chegar nessa mineração: "Sérgio silva";"1199900001";"n/i";"n/i";"Amil One Health";"n/i";"n/i";"2";"n/i";"2";

    Como eu faço para alocar cada valor em campo campo qualquer, pode ser uma tabela temporária, tipo assim:

    |nome           |campo 1        |Campo 2 |Campo 3 |Campo 4               |Campo  5 | .....

    |Sérgio silva|1199900001|n/i              |n/i              |Amil One Health|n/i               |n/i|2|n/i|2| .....

    Muito obrigado amigos


    • Editado RP Neto quarta-feira, 10 de julho de 2019 20:34
    quarta-feira, 10 de julho de 2019 20:34

Respostas

  • "X",

    pensei inicialmente que o texto fosse um objeto JSON. De qualquer forma há uma lógica no texto:
      i:   index - informa a identificação do campo
      s:  string - tamanho e texto do campo

    Por exemplo:
         i:18 -> campo 18
         s:13 -> texto com 13 caracteres

    O valor de s: informa um valor próximo.

    Ou seja, deve existir uma forma simples de decompor o texto em suas partes originais; você não possui informação sobre qual é a origem do texto?

    Outra dúvida: é garantido que serão sempre os mesmos campos (valores de i:) e na mesma ordem?

    ---

    Independente disso, parece que será possível obter os dados que necessita. Eis o passo a passo:

    (1) Obter o miolo, isto é, o que está delimitado por "{" e "}"

    i:18;s:13:"Sérgio silva";i:19;s:11:"1199900001";i:20;s:0:"n/i";i:21;s:0:"n/i";i:23;s:15:"Amil One Health";i:24;s:0:"n/i";i:27;s:0:"n/i";i:31;s:1:"2";i:32;s:0:"n/i";i:33;s:1:"2";i:34;s:0:;

    (2) Separar os campos do miolo, considerando-se ";" como separador

    i:18
    s:13:"Sérgio silva"
    i:19
    s:11:"1199900001"
    i:20
    s:0:"n/i"
    i:21
    s:0:"n/i"
    etc.

    (3) selecionar os campos do tipo "s"

    s:13:"Sérgio silva"
    s:11:"1199900001"
    s:0:"n/i"
    s:0:"n/i"
    etc.

    (4) Obter o terceiro elemento de cada campo "s", retirando ""

    Sérgio silva
    1199900001
    n/i
    n/i
    etc.

    (5) Tranformar tudo em colunas

    Sérgio silva | 1199900001 | n/i | n/i

    ---

    Eis o código:

    -- código #1 v3
    --Autor: José Diz/BH

    with
    -- obtém o trecho entre "{" e "}"
    Passo1 as (
    SELECT id_unica= row_number() over (order by (SELECT 0)),
           substring (registro,
                      charindex ('{', registro) +1,
                      (charindex ('}', registro) - charindex ('{', registro) -1)) as Miolo
      from tabela
    ),

    -- separa token por ";"
    Passo2 as (
    SELECT P1.id_unica,
           SS.ItemNumber as seq_2,
           SS.Item as Token
     from Passo1 as P1
          cross apply dbo.DelimitedSplit8K (P1.Miolo, ';') as SS
    ),

    -- seleciona tokens do tipo "s"
    Passo3 as (
    SELECT id_unica, Token,
           row_number() over (order by seq_2) as Coluna
      from Passo2
      where left (Token, 2) = 's:'
    ),

    -- obter o terceiro elemento de cada token
    Passo4 as (
    SELECT P3.id_unica, P3.Coluna,
           replace (SS.Item, '"', '') as Elemento
     from Passo3 as P3
          cross apply dbo.DelimitedSplit8K (P3.Token, ':') as SS
     where SS.ItemNumber = 3
    )

    SELECT id_unica, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
      from Passo4
           pivot (max (Elemento) for Coluna in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) as P;

    No passo 1, substitua tabela pelo nome da tabela e registro pelo nome da coluna que contém o texto.

    Foi utilizada a função de usuário dbo.DelimitedSplit8K() nos passos 2 e 4, pois a função nativa STRING_SPLIT() não é confiável para casos em que se necessita retornar a posição original. Aquela função, cujo autor é Jeff Moden, está no artigo “Separar conteúdo de texto multivalorado”. O uso de função do tipo "string split" permitiu separar facilmente os vários campos, primeiro pelo separador ";" e depois pelo separador ":".

    Para o passo 5 foi utilizado o operador PIVOT para montar a linha com as colunas. Detalhes no artigo “Alas & Pivôs”, que foi escrito a partir de dúvidas sobre pivô recorrentes em fóruns de SQL Server. O texto do artigo inicia pelos casos mais simples, como o seu, indo até os mais complexos.
     


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.
    • Sugerido como Resposta Junior Galvão - MVPMVP quarta-feira, 10 de julho de 2019 23:02
    • Não Sugerido como Resposta José Diz quinta-feira, 11 de julho de 2019 09:45
    • Marcado como Resposta RP Neto quinta-feira, 11 de julho de 2019 13:57
    • Editado José Diz quinta-feira, 11 de julho de 2019 17:11
    quarta-feira, 10 de julho de 2019 22:11
  • Além de acrescentar a coluna RA, houve outra alteração no código? É que estranhei o [2], [4], [6], ..

    Resp: Sim, alterei o código para adaptar a necessidade da empresa, segue a estrutura:

    A alteração que fez no passo 3, ao retirar a definição de Coluna, foi a causa do  [2], [4], ... no lugar de [1], [2], ...

    Implementei no passo 4 o replace(), para retirar as aspas.

    No passo 4 somente pode ter 3 colunas: RA, Coluna, Elemento;  senão o pivô fica errado. O acréscimo de P3.TOKEN foi que gerou a multiplicação de linhas.

    ---

    Veja o resultado que obtive, rodando o código #1 v3 original:

    ---

    Eis alteração no código #1, acrescentando a rotina de leitura que você informou e utilizando a coluna RA:

    -- código #2
    --Autor: José Diz/BH
    
    with 
    Mineracao as (
    SELECT alumatricula as RA, REPLACE (CONVERT(VARCHAR(MAX),dados_especificos),'""','"n/i"') AS FIELD
      FROM OPENQUERY (WEBSQL, 'select alumatricula, dados_especificos from webc.cliente_saude') as TB
    --WHERE alumatricula = '09867' ), -- obtém o trecho entre "{" e "}" Passo1 as ( SELECT RA, substring (FIELD, charindex ('{', FIELD) +1, (charindex ('}', FIELD) - charindex ('{', FIELD) -1)) as Miolo from Mineracao ), -- separa token por ";" Passo2 as ( SELECT P1.RA, SS.ItemNumber as seq_2, SS.Item as Token from Passo1 as P1 cross apply dbo.DelimitedSplit8K (P1.Miolo, ';') as SS ), -- seleciona tokens do tipo "s" Passo3 as ( SELECT RA, Token, row_number() over (order by seq_2) as Coluna from Passo2 where left (Token, 2) = 's:' ), -- obter o terceiro elemento de cada token Passo4 as ( SELECT P3.RA, P3.Coluna, replace (SS.Item, '"', '') as Elemento from Passo3 as P3 cross apply dbo.DelimitedSplit8K (P3.Token, ':') as SS where SS.ItemNumber = 3 ) SELECT RA, [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] from Passo4 pivot (max (Elemento) for Coluna 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] )) as P;

    Se tiver que filtrar dados, faça na CTE Mineracao; isto é, diretamente na rotina de leitura. Preferencialmente no código SQL que é enviado para o servidor remoto.

    ---

    Observei que está utilizando vinculação de servidores (linked server), para ler os dados de outro servidor. Um dos gargalos nesse caso é a transferência de dados pela rede. Assim, deve-se tentar reduzir ao máximo o tráfego na rede, para agilizar o processamento da consulta. Na CTE Mineracao foi alterado o comando de leitura remota, de modo a transferir somente as colunas alumatricula e dados_especificos.

    O código SQL estava
         'select * from webc.cliente_saude'
    e foi alterado para   
         'select alumatricula, dados_especificos from webc.cliente_saude'

    Se existem outras colunas, além das alumatricula e dados_especificos, então haverá menos dados a transmitir e a leitura será processada de forma mais rápida. Sobre esse assunto sugiro a leitura do artigo “Programação e otimização de consultas distribuídas”, que contém técnicas de otimização de consultas distribuídas.

     


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Marcado como Resposta RP Neto terça-feira, 16 de julho de 2019 14:31
    • Editado José Diz terça-feira, 16 de julho de 2019 15:46
    quinta-feira, 11 de julho de 2019 20:03
  • Mestre José :) ... sei q vc não gosta desse titulo, mas continua sendo mestre.

    Desculpe pela demora em dar um retorno.

    Deu tudo certo, consegui obter os resultados conforme a sua orientação.

    No caso do linked server, agradeço a você, pois eu li a sua matéria no likendin e coloquei em pratica a metodologia.

    Irei seguir a sua orientação.

    Novamente eu agradeço a sua atenção.

    abs e sucesso.

    • Marcado como Resposta RP Neto terça-feira, 16 de julho de 2019 14:31
    terça-feira, 16 de julho de 2019 14:08

Todas as Respostas

  • "X",

    pensei inicialmente que o texto fosse um objeto JSON. De qualquer forma há uma lógica no texto:
      i:   index - informa a identificação do campo
      s:  string - tamanho e texto do campo

    Por exemplo:
         i:18 -> campo 18
         s:13 -> texto com 13 caracteres

    O valor de s: informa um valor próximo.

    Ou seja, deve existir uma forma simples de decompor o texto em suas partes originais; você não possui informação sobre qual é a origem do texto?

    Outra dúvida: é garantido que serão sempre os mesmos campos (valores de i:) e na mesma ordem?

    ---

    Independente disso, parece que será possível obter os dados que necessita. Eis o passo a passo:

    (1) Obter o miolo, isto é, o que está delimitado por "{" e "}"

    i:18;s:13:"Sérgio silva";i:19;s:11:"1199900001";i:20;s:0:"n/i";i:21;s:0:"n/i";i:23;s:15:"Amil One Health";i:24;s:0:"n/i";i:27;s:0:"n/i";i:31;s:1:"2";i:32;s:0:"n/i";i:33;s:1:"2";i:34;s:0:;

    (2) Separar os campos do miolo, considerando-se ";" como separador

    i:18
    s:13:"Sérgio silva"
    i:19
    s:11:"1199900001"
    i:20
    s:0:"n/i"
    i:21
    s:0:"n/i"
    etc.

    (3) selecionar os campos do tipo "s"

    s:13:"Sérgio silva"
    s:11:"1199900001"
    s:0:"n/i"
    s:0:"n/i"
    etc.

    (4) Obter o terceiro elemento de cada campo "s", retirando ""

    Sérgio silva
    1199900001
    n/i
    n/i
    etc.

    (5) Tranformar tudo em colunas

    Sérgio silva | 1199900001 | n/i | n/i

    ---

    Eis o código:

    -- código #1 v3
    --Autor: José Diz/BH

    with
    -- obtém o trecho entre "{" e "}"
    Passo1 as (
    SELECT id_unica= row_number() over (order by (SELECT 0)),
           substring (registro,
                      charindex ('{', registro) +1,
                      (charindex ('}', registro) - charindex ('{', registro) -1)) as Miolo
      from tabela
    ),

    -- separa token por ";"
    Passo2 as (
    SELECT P1.id_unica,
           SS.ItemNumber as seq_2,
           SS.Item as Token
     from Passo1 as P1
          cross apply dbo.DelimitedSplit8K (P1.Miolo, ';') as SS
    ),

    -- seleciona tokens do tipo "s"
    Passo3 as (
    SELECT id_unica, Token,
           row_number() over (order by seq_2) as Coluna
      from Passo2
      where left (Token, 2) = 's:'
    ),

    -- obter o terceiro elemento de cada token
    Passo4 as (
    SELECT P3.id_unica, P3.Coluna,
           replace (SS.Item, '"', '') as Elemento
     from Passo3 as P3
          cross apply dbo.DelimitedSplit8K (P3.Token, ':') as SS
     where SS.ItemNumber = 3
    )

    SELECT id_unica, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
      from Passo4
           pivot (max (Elemento) for Coluna in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) as P;

    No passo 1, substitua tabela pelo nome da tabela e registro pelo nome da coluna que contém o texto.

    Foi utilizada a função de usuário dbo.DelimitedSplit8K() nos passos 2 e 4, pois a função nativa STRING_SPLIT() não é confiável para casos em que se necessita retornar a posição original. Aquela função, cujo autor é Jeff Moden, está no artigo “Separar conteúdo de texto multivalorado”. O uso de função do tipo "string split" permitiu separar facilmente os vários campos, primeiro pelo separador ";" e depois pelo separador ":".

    Para o passo 5 foi utilizado o operador PIVOT para montar a linha com as colunas. Detalhes no artigo “Alas & Pivôs”, que foi escrito a partir de dúvidas sobre pivô recorrentes em fóruns de SQL Server. O texto do artigo inicia pelos casos mais simples, como o seu, indo até os mais complexos.
     


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.
    • Sugerido como Resposta Junior Galvão - MVPMVP quarta-feira, 10 de julho de 2019 23:02
    • Não Sugerido como Resposta José Diz quinta-feira, 11 de julho de 2019 09:45
    • Marcado como Resposta RP Neto quinta-feira, 11 de julho de 2019 13:57
    • Editado José Diz quinta-feira, 11 de julho de 2019 17:11
    quarta-feira, 10 de julho de 2019 22:11
  • Mestre José Diz, muito obrigado pela orientação.

    Deu certo, a unica coisa que tive que criar foi DelimitedSplit8K, encontrei o codigo completo na web.

    Muito obrigado, mestre.

    Valeu mesmo.

    para quem não tem o código DelimitedSplit8K, segue abaixo:

    CREATE FUNCTION [dbo].[DelimitedSplit8K]
    --===== Define I/O parameters
            (@pString VARCHAR(8000), @pDelimiter CHAR(1))
    --WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
    RETURNS TABLE WITH SCHEMABINDING AS
     RETURN
    --===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
         -- enough to cover VARCHAR(8000)
      WITH E1(N) AS (
                     SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                     SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                     SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                    ),                          --10E+1 or 10 rows
           E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
           E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
     cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                         -- for both a performance gain and prevention of accidental "overruns"
                     SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                    ),
    cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                     SELECT 1 UNION ALL
                     SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                    ),
    cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                     SELECT s.N1,
                            ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                       FROM cteStart s
                    )
    --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
     SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
            Item       = SUBSTRING(@pString, l.N1, l.L1)
       FROM cteLen l
    ;

    quinta-feira, 11 de julho de 2019 13:57
  • Deu certo, a unica coisa que tive que criar foi DelimitedSplit8K, encontrei o codigo completo na web.

    "X",

    o código fonte dela está no final do artigo “Separar conteúdo de texto multivalorado”. Faltou eu explicar isso melhor na resposta e que é necessário criar a função antes de executar o código. De qualquer forma, se você leu o artigo está a par das várias formas de separar texto multivalorado; é bom conhecê-las, pois elas são úteis no dia a dia.

    Para resolver o seu problema foram utilizadas 3 ferramentas:

    • uso de CTE encadeda;
    • função do tipo "string split"
    • pivô.

     

    Cada uma delas está descrita em um artigo. Dois deles foram mencionados na resposta anterior e o terceiro, uso de CTE (common table expression) encadeadas, torna o código modular, fácil de compreender e de dar manutenção. O uso dessa técnica está descrita no artigo “Programação modular com expressões de tabela”.

    Só falta você informar o seu prenome!  rs


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]



    • Editado José Diz quinta-feira, 11 de julho de 2019 14:47
    quinta-feira, 11 de julho de 2019 14:39
  • Mestre, José!

    Perfeito, já estou com o conteúdo em PDF para ler calmamente e simular aqui.

    Por favor, só mais uma ajuda :)

    Como eu faço, para agrupar o resultado em uma unica linha, tirando todos esses Null's, assim:

    Como está atualmente:


    Preciso que fique assim:

    Novamente, sou muito grato á você mestre José Diz.

    quinta-feira, 11 de julho de 2019 14:56
  • Como eu faço, para agrupar o resultado em uma unica linha, tirando todos esses Null's, assim:

    Nos testes que fiz com o código #1 v2, o resultado final foi uma única linha e com cabeçalhos [1], [2], [3], ...

    Além de acrescentar a coluna RA, houve outra alteração no código? É que estranhei o [2], [4], [6], ..

    ---

    RA é a chave primária da tabela que contém os textos multivalorados? Se for, eis os passos:

    (1)
    Na CTE Passo1, altere
          SELECT id_unica= row_number() over (order by (SELECT 0)),
    para
          SELECT RA,

    (2)
    No restante do código, substitua
         id_unica
    por
         RA

    Por exemplo, se estiver
         P1.id_unica
    altere para
         P1.RA

      

    (3)
    Se são 28 colunas, então na CTE Passo4 altere
           and Coluna <= 10
    para
           and Coluna <= 28


    (4)

    No passo 5, nos dois pontos onde está
          [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
    acrescente os valores de [11] a [28], ficando então assim:
         [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12],     ....    [28]

    ---

    É provável que o a:28 informe a quantidade de atributos dentro de {}.

    Você rodou o código #1 v2?


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz quinta-feira, 11 de julho de 2019 15:50
    quinta-feira, 11 de julho de 2019 15:46
  • Além de acrescentar a coluna RA, houve outra alteração no código? É que estranhei o [2], [4], [6], ..

    Resp: Sim, alterei o código para adaptar a necessidade da empresa, segue a estrutura:

    WITH 
    -- OBTÉM O TRECHO ENTRE "{" E "}"
    PASSO1 AS (
    SELECT ID_UNICA= ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
           alumatricula as RA,
           SUBSTRING (FIELD, 
                      CHARINDEX ('{', FIELD) +1,
                      (CHARINDEX ('}', FIELD) - CHARINDEX ('{', FIELD) -1)) AS MIOLO
      FROM 
      (SELECT alumatricula, REPLACE (CONVERT(VARCHAR(MAX),dados_especificos),'""','"n/i"') AS FIELD
    FROM (SELECT * FROM OPENQUERY(WEBSQL, 'select * from webc.cliente_saude') ) AS TB
    ) AS MINERACAO
    ),

    -- separa token por ";"
    PASSO2 AS (
    SELECT P1.RA,
       P1.ID_UNICA, 
           SS.ITEMNUMBER AS SEQ_2,
           SS.ITEM AS TOKEN
     FROM PASSO1 AS P1
          CROSS APPLY DBO.DELIMITEDSPLIT8K (P1.MIOLO, ';') AS SS
      ),

    -- seleciona tokens do tipo "s"
    PASSO3 AS (
    SELECT ID_UNICA, 
       TOKEN, 
       SEQ_2,
       RA
      FROM PASSO2
      WHERE LEFT (TOKEN, 2) = 's:'
    ),

    -- obter o terceiro elemento de cada token
    PASSO4 AS (
    SELECT  P3.ID_UNICA, 
    P3.SEQ_2 AS COLUNA, 
    P3.TOKEN, 
    P3.RA,
    REPLACE (SS.ITEM,'"','') AS ELEMENTO
     FROM PASSO3 AS P3
          CROSS APPLY DBO.DELIMITEDSPLIT8K (P3.TOKEN, ':') AS SS
    WHERE SS.ITEMNUMBER = 3
    ),

    PASSO5 AS
    (
    SELECT RA, ID_UNICA, [2], [4], [6], [8], [10], [12], [14], [16], [18], [20], [22], [24], [26], [28], [30], [32], [34], [36], [38], [40], [42], [44], [46], [48], [50], [52], [54], [56]
      from Passo4 
          pivot (MAX (ELEMENTO) for COLUNA in ([2], [4], [6], [8], [10], [12], [14], [16], [18], [20], [22], [24], [26], [28], [30], [32], [34], [36], [38], [40], [42], [44], [46], [48], [50], [52], [54], [56])) as P



    SELECT * FROM PASSO5 WHERE RA = '09867'


    RA é a chave primária da tabela que contém os textos multivalorados? 

    Resp: Sim.

    Você rodou o código #1 v2?

    Resp: Rodei, segue os resultados

    Irei seguir a sua sugestão para tentar agrupar em uma unica linha os dados, logo postarei o resultado.


    quinta-feira, 11 de julho de 2019 16:09
  • Além de acrescentar a coluna RA, houve outra alteração no código? É que estranhei o [2], [4], [6], ..

    Resp: Sim, alterei o código para adaptar a necessidade da empresa, segue a estrutura:

    A alteração que fez no passo 3, ao retirar a definição de Coluna, foi a causa do  [2], [4], ... no lugar de [1], [2], ...

    Implementei no passo 4 o replace(), para retirar as aspas.

    No passo 4 somente pode ter 3 colunas: RA, Coluna, Elemento;  senão o pivô fica errado. O acréscimo de P3.TOKEN foi que gerou a multiplicação de linhas.

    ---

    Veja o resultado que obtive, rodando o código #1 v3 original:

    ---

    Eis alteração no código #1, acrescentando a rotina de leitura que você informou e utilizando a coluna RA:

    -- código #2
    --Autor: José Diz/BH
    
    with 
    Mineracao as (
    SELECT alumatricula as RA, REPLACE (CONVERT(VARCHAR(MAX),dados_especificos),'""','"n/i"') AS FIELD
      FROM OPENQUERY (WEBSQL, 'select alumatricula, dados_especificos from webc.cliente_saude') as TB
    --WHERE alumatricula = '09867' ), -- obtém o trecho entre "{" e "}" Passo1 as ( SELECT RA, substring (FIELD, charindex ('{', FIELD) +1, (charindex ('}', FIELD) - charindex ('{', FIELD) -1)) as Miolo from Mineracao ), -- separa token por ";" Passo2 as ( SELECT P1.RA, SS.ItemNumber as seq_2, SS.Item as Token from Passo1 as P1 cross apply dbo.DelimitedSplit8K (P1.Miolo, ';') as SS ), -- seleciona tokens do tipo "s" Passo3 as ( SELECT RA, Token, row_number() over (order by seq_2) as Coluna from Passo2 where left (Token, 2) = 's:' ), -- obter o terceiro elemento de cada token Passo4 as ( SELECT P3.RA, P3.Coluna, replace (SS.Item, '"', '') as Elemento from Passo3 as P3 cross apply dbo.DelimitedSplit8K (P3.Token, ':') as SS where SS.ItemNumber = 3 ) SELECT RA, [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] from Passo4 pivot (max (Elemento) for Coluna 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] )) as P;

    Se tiver que filtrar dados, faça na CTE Mineracao; isto é, diretamente na rotina de leitura. Preferencialmente no código SQL que é enviado para o servidor remoto.

    ---

    Observei que está utilizando vinculação de servidores (linked server), para ler os dados de outro servidor. Um dos gargalos nesse caso é a transferência de dados pela rede. Assim, deve-se tentar reduzir ao máximo o tráfego na rede, para agilizar o processamento da consulta. Na CTE Mineracao foi alterado o comando de leitura remota, de modo a transferir somente as colunas alumatricula e dados_especificos.

    O código SQL estava
         'select * from webc.cliente_saude'
    e foi alterado para   
         'select alumatricula, dados_especificos from webc.cliente_saude'

    Se existem outras colunas, além das alumatricula e dados_especificos, então haverá menos dados a transmitir e a leitura será processada de forma mais rápida. Sobre esse assunto sugiro a leitura do artigo “Programação e otimização de consultas distribuídas”, que contém técnicas de otimização de consultas distribuídas.

     


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Marcado como Resposta RP Neto terça-feira, 16 de julho de 2019 14:31
    • Editado José Diz terça-feira, 16 de julho de 2019 15:46
    quinta-feira, 11 de julho de 2019 20:03
  • Mestre José :) ... sei q vc não gosta desse titulo, mas continua sendo mestre.

    Desculpe pela demora em dar um retorno.

    Deu tudo certo, consegui obter os resultados conforme a sua orientação.

    No caso do linked server, agradeço a você, pois eu li a sua matéria no likendin e coloquei em pratica a metodologia.

    Irei seguir a sua orientação.

    Novamente eu agradeço a sua atenção.

    abs e sucesso.

    • Marcado como Resposta RP Neto terça-feira, 16 de julho de 2019 14:31
    terça-feira, 16 de julho de 2019 14:08