the following query returns two fields (DATA1 AND DEPARTMENT ) WHICH 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 DEPARTMENT field... as you will see is a subquery, and with the "for xml path" I go through the results, concatenate them and put a comma... as a separator, but the problem is that it also adds the comma in the last record.
for instance
returns = a,b,c,d,e,
I want = a,b,c,d,e
how do i do it?
If we put the comma at the beginning instead of at the end it is easier to remove it using the STUFF function
STUFF
can insert and remove characters from a string at a given position and lengthDo it the other way around and remove the first comma, using the stuff function :
So:
stuff
Edited: The answer doesn't apply as much after the last edit to the question. But I'm going to 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, as far as moving the comma to the beginning and using
stuff
, are totally correct. I just want to specify that depending on how long the string you use to separate the elements is, you should adjust the third parameter ofstuff
.For example, in your case,
youwere using' , '
(a comma surrounded by 3 spaces on each side), that is 7 characters.This being the case, you need it to
stuff
remove the first 7 characters, so use7
as the third parameter: