GUJ Discussões   :   últimos tópicos   |   categorias   |   GUJ Respostas

[resolvido] Sql server - Como remover o último caracteres dessa consulta

sqlserver
Tags: #<Tag:0x00007fb3c7e82ad8>

#1

Select
MAS_DOMICILIO.MICROAREA_ID,
Coalesce((Select Cast(D.DOM_IDENTIFICACAO As VARCHAR(10)) +’,’ As identificador From MAS_DOMICILIO As D
Where MAS_DOMICILIO.MICROAREA_ID = D.MICROAREA_ID Order By D.DOM_IDENTIFICACAO For Xml Path(’’), Type).value(’.[1]’, ‘VARCHAR(MAX)’), ‘’) As LISTA_IDENTIFICADOR
From
MAS_DOMICILIO
Group By
MAS_DOMICILIO.MICROAREA_ID

a consulta retorna o seguinte
20,21,22,23,24,
eu quero remover a última virgual


SQL SERVER - Remover último caracter
#2

Usa STUFF. Exemplo:

    select 
       t1.grupo, 
       stuff((select ',' + cast(t2.campo as varchar(10)) from tabela t2 where t2.grupo = t1.grupo for xml path ('')), 1, 1, '')
    from
       tabela t1
    group by
       t1.grupo

https://docs.microsoft.com/pt-br/sql/t-sql/functions/stuff-transact-sql


#3

resolvi dessa forma.

Select
MAS_DOMICILIO.MICROAREA_ID,
Left(Coalesce((Select Cast(D.DOM_IDENTIFICACAO As VARCHAR(10)) + ‘,’ As [text()] From MAS_DOMICILIO As D Where MAS_DOMICILIO.MICROAREA_ID = D.MICROAREA_ID Order By D.DOM_IDENTIFICACAO For Xml Path(’’), Type).value(’.[1]’, ‘VARCHAR(MAX)’), ‘’), Len(Coalesce((Select Cast(D.DOM_IDENTIFICACAO As VARCHAR(10)) + ‘,’ As [text()] From MAS_DOMICILIO As D Where MAS_DOMICILIO.MICROAREA_ID = D.MICROAREA_ID Order By D.DOM_IDENTIFICACAO For Xml Path(’’), Type).value(’.[1]’, ‘VARCHAR(MAX)’), ‘’)) - 1) As LISTA_IDENTIFICADOR
From
MAS_DOMICILIO
Group By
MAS_DOMICILIO.MICROAREA_ID