How to delete the last comma of a concatenation of a sub query in sql? using XML PATH

Question

How to delete the last comma of a concatenation of a sub query in sql? using XML PATH

Navigation

#1 by (2 votes)
#2 by (2 votes)
#3 by (1 votes)

1

the following query returns two fields (DATA1 AND DEPARTMENT) THAT ARE VARCHAR

SELECT DATO1 ,(SELECT   DD.NombreDepartamento +  ','  FROM CIALES AS CT INNER JOIN Departamento AS DD 
    ON CT.ID_DEPARTAMENTO = DD.IdDepartamento 
    ORDER BY CT.ID DESC 
    FOR XML PATH('') )
 AS DEPARTAMENTO FROM TABLA

The problem I have is that the field DEPARTMENT .. as you will see is a subquery, and that with the "for xml path" I go through the results, I concatenate them and put a comma as a separator, but the problem is that also add the comma in the last record.

for example

returns = a, b, c, d, e,

I want = a, b, c, d, e

How do I do it?

sql sql-server

asked by Gerard_jcr 04.11.2016 в 23:54

source

3 answers

2

Do it in reverse and remove the first comma, using the function stuff :

Like this:

select dato1
       , stuff(
           (select ', ' + DD.NombreDepartamento
              from ciales as ct
                   inner join Departamento as dd on ct.id_departamento = dd.IdDepartamento
                   where p.id = ct.id
                   order by ct.id desc
                     for xml path('')
           )
           , 1, 2, '') as Departamento
  from tabla

stuff

The STUFF function inserts one string into another. Remove a certain length of characters from the first string from the start position, and then insert the second string into the first one, in the start position.

answered by 05.11.2016 в 00:21

2

If we put the comma at the beginning instead of at the end it is easier to remove it using the function STUFF

SELECT STUFF((SELECT ',' + Columna)
              FROM Tabla
              FOR XML PATH ('')), 1, 1, '')

STUFF can insert and remove characters from a string at a given position and length

STUFF(...., -- cadena a modificar
      1, -- posición desde donde se modifica (empieza en 1, no en 0)
      1, -- cuantos caracteres se van a quitar
      '' -- cadena a insertar (vacío)
     )

answered by 05.11.2016 в 00:18

How to include a load image (Loading), ajax execution Show current date and time in C ++

score 1 · Accepted Answer

Edited: The answer does not apply so much after the last issue to the question. But I'll leave the answer in case it helps someone in terms of adjusting the third parameter of stuff depending on the string used to separate the elements.

The other answers, in terms of moving the comma at the beginning and using stuff , are completely correct. I just want to specify that depending on how long the string you use is to separate the elements, you should adjust the third parameter of stuff .

For example, in your case, ~~you are~~ you were using ' , ' (a comma surrounded by 3 spaces on each side), that is 7 characters.

This being the case, you need stuff to remove the first 7 characters, so you should use 7 as the third parameter:

 select dato1,
        stuff((select '   ,   ' + dd.NombreDepartamento
           from ciales as ct
           join Departamento as dd
             on ct.id_departamento = dd.IdDepartamento
          where p.id = ct.id
          order by ct.id desc
          for xml path('')), 1, 7, '') as departamento
from tabla