CODEWITHSUNDEEP
sql-serverxmlsql-server-2014
GuidoG
GuidoG

Reputation: 12014

How to remove space when concatenating data from different rows into one column using xml?

I am trying to combine data from different rows into one column, and this is working with just one minor problem.

declare @RitID int = 16

select ...,

       ( select distinct
                ISNULL(LTRIM(RTRIM(r2.LotNr)), LTRIM(RTRIM(r.LotNr))) + '+' as 'data()'
         from   tblExtraBestemming eb2   
           inner join tblRit r2 on eb2.RitID = r2.RitID
         where  eb2.BestemmingID = eb.BestemmingID
         and    eb2.BestemmingTypeID = eb.BestemmingTypeID
         and    (  (eb.CombinedChildExtraBestemmingID is null and eb2.RitID = @RitID)
                   or
                   (eb.CombinedChildExtraBestemmingID is not null and eb2.RitID in (select r4.RitID from tblRit r4 where r4.MasterRitID = @RitID) )
                )
         for XML PATH('')
       ) as LotNr
from tblExtraBestemming eb
where ...

this returns the correct data for the column LotNr, like this 

GTT18196
GTT18197
GTT18198+ GTT18199

Now my only problem is the space after the + sign in the third row from the result, how can I get rid of this ?

I expect this result 

GTT18196
GTT18197
GTT18198+GTT18199

PS, actually there is also a + at the end of each row, but that is removed by the client. I thought I better mentions this already.

EDIT
I checked the data, there are no spaces at the end or the beginning of the data

EDIT
Query updated as suggested by @Larnu

EDIT
if I check the data in the table, this is the result 

select '/' + r.LotNr + '/' from tblRit r where r.RitID in (50798, 50799)

COLUMN1 
------- 
/GTT18198/  
/GTT18199/

So it appears to me there are no characters before or after the data

Upvotes: 0

Views: 97

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272006

Just remove AS 'data()' from your query (it is not required in the above case).

And if trailing + is a problem, move it to the beginning and use STUFF function to chop off the first character from the result.

Upvotes: 1

Related Questions