none
Utilizar o cte com base no script abaixo - dinâmico RRS feed

  • Pergunta

  • Pessoal

        Vendo o conceito de cte e vendo que ele de certa forma estaria utilizando algo próximo de recursividade, no script abaixo, eu conseguiria deixar ele mais prático/dinâmico utilizando o cte ?

    
    
    select
    nmUnidade,id_Unidade,
    case
    when [30_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto],'#',[13_ponto],'#',[14_ponto],'#',[15_ponto],'#',[16_ponto],'#',[17_ponto],'#',[18_
    fat],'#',[19_ponto],'#',[20_ponto],'#',[21_ponto],'#',[22_ponto],'#',[23_ponto],'#',[24_ponto],'#',[25_ponto],'#',[26_ponto],'#',[27_ponto],'#',[28_ponto],'#',[29_ponto],'#',[30_ponto])
    when [29_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto],'#',[13_ponto],'#',[14_ponto],'#',[15_ponto],'#',[16_ponto],'#',[17_ponto],'#',[18_f
    at],'#',[19_ponto],'#',[20_ponto],'#',[21_ponto],'#',[22_ponto],'#',[23_ponto],'#',[24_ponto],'#',[25_ponto],'#',[26_ponto],'#',[27_ponto],'#',[28_ponto],'#',[29_ponto])
    when [28_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto],'#',[13_ponto],'#',[14_ponto],'#',[15_ponto],'#',[16_ponto],'#',[17_ponto],'#',[18_f
    at],'#',[19_ponto],'#',[20_ponto],'#',[21_ponto],'#',[22_ponto],'#',[23_ponto],'#',[24_ponto],'#',[25_ponto],'#',[26_ponto],'#',[27_ponto],'#',[28_ponto])
    when [27_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto],'#',[13_ponto],'#',[14_ponto],'#',[15_ponto],'#',[16_ponto],'#',[17_ponto],'#',[18_f
    at],'#',[19_ponto],'#',[20_ponto],'#',[21_ponto],'#',[22_ponto],'#',[23_ponto],'#',[24_ponto],'#',[25_ponto],'#',[26_ponto],'#',[27_ponto])
    when [26_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto],'#',[13_ponto],'#',[14_ponto],'#',[15_ponto],'#',[16_ponto],'#',[17_ponto],'#',[18_f
    at],'#',[19_ponto],'#',[20_ponto],'#',[21_ponto],'#',[22_ponto],'#',[23_ponto],'#',[24_ponto],'#',[25_ponto],'#',[26_ponto])
    when [25_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto],'#',[13_ponto],'#',[14_ponto],'#',[15_ponto],'#',[16_ponto],'#',[17_ponto],'#',[18_f
    at],'#',[19_ponto],'#',[20_ponto],'#',[21_ponto],'#',[22_ponto],'#',[23_ponto],'#',[24_ponto],'#',[25_ponto])
    when [24_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto],'#',[13_ponto],'#',[14_ponto],'#',[15_ponto],'#',[16_ponto],'#',[17_ponto],'#',[18_f
    at],'#',[19_ponto],'#',[20_ponto],'#',[21_ponto],'#',[22_ponto],'#',[23_ponto],'#',[24_ponto])
    when [23_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto],'#',[13_ponto],'#',[14_ponto],'#',[15_ponto],'#',[16_ponto],'#',[17_ponto],'#',[18_f
    at],'#',[19_ponto],'#',[20_ponto],'#',[21_ponto],'#',[22_ponto],'#',[23_ponto])
    when [22_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto],'#',[13_ponto],'#',[14_ponto],'#',[15_ponto],'#',[16_ponto],'#',[17_ponto],'#',[18_f
    at],'#',[19_ponto],'#',[20_ponto],'#',[21_ponto],'#',[22_ponto])
    when [21_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto],'#',[13_ponto],'#',[14_ponto],'#',[15_ponto],'#',[16_ponto],'#',[17_ponto],'#',[18_f
    at],'#',[19_ponto],'#',[20_ponto],'#',[21_ponto])
    when [20_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto],'#',[13_ponto],'#',[14_ponto],'#',[15_ponto],'#',[16_ponto],'#',[17_ponto],'#',[18_f
    at],'#',[19_ponto],'#',[20_ponto])
    when [19_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto],'#',[13_ponto],'#',[14_ponto],'#',[15_ponto],'#',[16_ponto],'#',[17_ponto],'#',[18_f
    at],'#',[19_ponto])
    when [18_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto],'#',[13_ponto],'#',[14_ponto],'#',[15_ponto],'#',[16_ponto],'#',[17_ponto],'#',[18_f
    at])
    when [17_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto],'#',[13_ponto],'#',[14_ponto],'#',[15_ponto],'#',[16_ponto],'#',[17_ponto])
    when [16_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto],'#',[13_ponto],'#',[14_ponto],'#',[15_ponto],'#',[16_ponto])
    when [15_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto],'#',[13_ponto],'#',[14_ponto],'#',[15_ponto])
    when [14_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto],'#',[13_ponto],'#',[14_ponto])
    when [13_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto],'#',[13_ponto])
    when [12_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto],'#',[12_ponto])
    when [11_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto],'#',[11_ponto])
    when [10_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto],'#',[10_ponto])
    when [9_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto],'#',[9_ponto])
    when [8_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto],'#',[8_ponto])
    when [7_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto],'#',[7_ponto])
    when [6_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto],'#',[6_ponto])
    when [5_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto],'#',[5_ponto])
    when [4_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto],'#',[4_ponto])
    when [3_ponto] is not null then concat([1_ponto],'#',[2_ponto],'#',[3_ponto])
    when [2_ponto] is not null then concat([1_ponto],'#',[2_ponto])
    when [1_ponto] is not null then [1_ponto]
    end as ponto,
    num_ponto
    into tb_catraca_filial_geral
    from tb_catraca_filial

    quarta-feira, 7 de outubro de 2020 14:56

Todas as Respostas

  • Neibala,

    O que deste script será dinâmico? Você pode utilizar variáveis como parâmetros para sua CTE.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 7 de outubro de 2020 23:03
  • Bom dia,

    neibala, qual versão do SQL Server você está utilizando?

    Quando uma coluna n_ponto é nula, todas as colunas posteriores também são?


    Assinatura: http://www.imoveisemexposicao.com.br

    quinta-feira, 8 de outubro de 2020 12:11
  • Junior Galvão / gapimex

         Veja em qual ponto poderia ter algo mais dinâmico, vendo esses pontos :

        Onde ele inicia de 1 para 30 na questão das linhas, já na questão das colunas, se estiver valor no campo ( 5_ponto ) ele estaria concatenando os valores das colunas até 5 e no caso do campo ( 25_ponto ) ele estaria concatenando os valores das colunas até 25.

          A versão que estou fazendo os testes é 2014 e sobre as colunas ser nula, isto pode ser aleatório.

    quinta-feira, 8 de outubro de 2020 20:09
  • Pode ocorrer por exemplo de a coluna 6_ponto ser nula e nessa mesma linha ter valor na 7_ponto?

    Assinatura: http://www.imoveisemexposicao.com.br

    quinta-feira, 8 de outubro de 2020 20:34
  • Junior Galvão / gapimex

         Veja em qual ponto poderia ter algo mais dinâmico, vendo esses pontos :

        Onde ele inicia de 1 para 30 na questão das linhas, já na questão das colunas, se estiver valor no campo ( 5_ponto ) ele estaria concatenando os valores das colunas até 5 e no caso do campo ( 25_ponto ) ele estaria concatenando os valores das colunas até 25.


    Neibala,

    Tomando como base esta sua frase: "A versão que estou fazendo os testes é 2014 e sobre as colunas ser nula, isto pode ser aleatório."

    Sou sincero, se você permitir este tipo de possibilidade, vai acabar ficando louco, pois para se analisar a anulabidade de cada coluna vai tornar o seu código algo muito complexo e bastante lento.



    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 8 de outubro de 2020 22:21
  • Junior / Grupo

          Sobre este assunto, para que possa entender melhor a estrutura, vamos considerar que não teremos nulo, neste caso como seria o script para que possa visualizar melhor a estrutura e verificar até que ponto ela estaria me atendendo. Então teria como me enviar um exemplo bem simples para que possa visualizar melhor como seria a estrutura do script ?

    terça-feira, 3 de novembro de 2020 18:03
  • Acho que uma alternativa para obter os valores das colunas concatenados seria utilizar a função String_Agg que está disponível no SQL Server 2014 que você disse que está utilizando.

    Mas para utilizar a função String_Agg, os valores das colunas teriam que ser retornados em linhas, então utilizei uma alternativa com o operador Apply e o construtor de tabela Values:

    select t.num_ponto, string_agg(ca.n_ponto, '#') as ponto
    from tb_catraca_filial as t
    cross apply
    (
      select * from 
      (
        values (t.[1_ponto]), (t.[2_ponto]), (t.[3_ponto]), (t.[4_ponto]), (t.[5_ponto])
      ) as v(n_ponto)
    ) as ca
    group by t.num_ponto

    obs: a função String_Agg desconsidera os nulos

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    quinta-feira, 5 de novembro de 2020 13:14
  • Acho que uma alternativa para obter os valores das colunas concatenados seria utilizar a função String_Agg que está disponível no SQL Server 2014 que você disse que está utilizando.

    Mas para utilizar a função String_Agg, os valores das colunas teriam que ser retornados em linhas, então utilizei uma alternativa com o operador Apply e o construtor de tabela Values:

    select t.num_ponto, string_agg(ca.n_ponto, '#') as ponto
    from tb_catraca_filial as t
    cross apply
    (
      select * from 
      (
        values (t.[1_ponto]), (t.[2_ponto]), (t.[3_ponto]), (t.[4_ponto]), (t.[5_ponto])
      ) as v(n_ponto)
    ) as ca
    group by t.num_ponto

    obs: a função String_Agg desconsidera os nulos

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    Gapimex,

    Muito interessante a sua sugestão, somente gostaria de destacar que a função String_AGG foi implementada a partir da versão 2017.

    Talvez uma outra alternativa que poderia ser aplicada a necessidade do Neibala, seja o uso da função Concat_WS


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 6 de novembro de 2020 22:04
  • Junior Galvão  / Gapimex / Grupo

          Agradeço a sugestão e estarei testando e depois dou um retorno.

          Vendo a execução

    sexta-feira, 6 de novembro de 2020 23:39
  • Junior Galvão  / Gapimex / Grupo

         Pessoal estava validando a rotina e percebi que a função ( Concat_WS ) também não é da versão 2014, só que vendo a estrutura do ( Gapimex ), eu conseguiria utilizar no lugar uma função ( stuff ) no lugar da função ( string_agg ), vendo que os dados estaria linha a linha cada informação, ao invés de tudo na mesma linha ?

    sábado, 14 de novembro de 2020 00:45
  • Sim, é possível utilizar a função stuff com a conversão para XML para concatenar os valores em um único valor, e pode ser dentro do Apply mesmo.

    Assinatura: http://www.imoveisemexposicao.com.br

    sábado, 14 de novembro de 2020 16:53
  • Junior Galvão  / Gapimex / Grupo

         Pessoal estava validando a rotina e percebi que a função ( Concat_WS ) também não é da versão 2014, só que vendo a estrutura do ( Gapimex ), eu conseguiria utilizar no lugar uma função ( stuff ) no lugar da função ( string_agg ), vendo que os dados estaria linha a linha cada informação, ao invés de tudo na mesma linha ?

    Neibala,

    Ok, perfeito, sim nós destacamos que a Concat_WS e String_AGG foram adicionadas na versão 2017.

    Sobre o uso da Stuff() você pode fazer uso desta função, inclusive combinando o retorno no formato XML.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    segunda-feira, 16 de novembro de 2020 23:15

  •  Gapimex / Junior Galvão / Grupo

         Você conseguiria me passar um exemplo utilizando com base neste seu apply com stuff, só que a geração seria no formato normal, sem ser xml ?

    terça-feira, 17 de novembro de 2020 05:02
  • Segue uma sugestão para testes:

    select t.num_ponto, ca.ponto
    from tb_catraca_filial as t
    cross apply
    (
      select 
        stuff
          ( (select '#' + v.n_ponto 
             from 
             (
               values (t.[1_ponto]), (t.[2_ponto]), (t.[3_ponto]), (t.[4_ponto]), (t.[5_ponto])
             ) as v(n_ponto)
             FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
            , 1, 1, '') as ponto
    ) as ca
    

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    terça-feira, 17 de novembro de 2020 17:42
  •  Gapimex 

       Vou testar aqui e depois eu te aviso.

    terça-feira, 17 de novembro de 2020 22:43
  • Neibala,

    Veja estes dois exemplos:

    Use ProjetoDWQueimadas
    Go
    
    -- Concatenando os nomes de municípios por Estado --
    Select EstadoMunicipio, 
              Ltrim(Stuff((Select ' | '+NomeMunicipio 
    		           From MunicipiosQueimadas M1
    				   Where M1.EstadoMunicipio = M2.EstadoMunicipio
    				   Order By M1.EstadoMunicipio 
    				   for xml path(''), TYPE).value('.', 'varchar(max)'),1,2,'')) As 'Relação de Municípios'
    From MunicipiosQueimadas M2
    Group By M2.EstadoMunicipio
    Order By EstadoMunicipio Asc
    Go
    
    -- Apresentando a quantidade de municípios por Estado --
    Select EstadoMunicipio, 
              Ltrim(Stuff((Select ' | '+NomeMunicipio 
    		           From MunicipiosQueimadas M1
    				   Where M1.EstadoMunicipio = M2.EstadoMunicipio
    				   Order By M1.EstadoMunicipio 
    				   for xml path(''), TYPE).value('.', 'varchar(max)'),1,2,'')) As 'Relação de Municípios',
    		Count(M2.NomeMunicipio) As 'Total de Municípios Por Estado'
    From MunicipiosQueimadas M2
    Group By M2.EstadoMunicipio
    Order By EstadoMunicipio Asc
    Go
    

    Se quiser saber mais, tem um post no meu blog justamente sobre este assunto: 

    https://pedrogalvaojunior.wordpress.com/2020/01/30/dica-do-mes-concatenando-a-relacao-de-municipios-por-estados-atraves-do-uso-da-funcao-stuff/

    https://pedrogalvaojunior.wordpress.com/2020/02/23/28-para-que-serve/


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 18 de novembro de 2020 14:20