CODEWITHSUNDEEP
sql-server
Ann
Ann

Reputation: 23

SQL XML PATH--HOW to remove the space in the middle

For the table:

table

I wanted to get the output like this:

1 ab
2 xyz

I use the query(SQL Server 2012):

SELECT ID, Name = STUFF((SELECT ' ' + Name
                     FROM [dbo].[a] AS a2
                     WHERE a2.ID = a.ID
                     ORDER BY ID
                     FOR XML PATH('')),1,1,'')
                  FROM [dbo].[a] AS a
GROUP BY ID
ORDER BY ID;

I got the result:

my result

How to remove the blank space in the middle?

Upvotes: 0

Views: 2725

Answers (2)

vamsi
vamsi

Reputation: 352

This should work

   SELECT ID, Name =  REPLACE((STUFF((SELECT ' ' + Name
                         FROM [dbo].[a] AS a2
                         WHERE a2.ID = a.ID
                         ORDER BY ID
                         FOR XML PATH('')),1,1,'')), ' ', '')
                      FROM [dbo].[a] AS a
group by id

Upvotes: 0

Biz. Nigatu
Biz. Nigatu

Reputation: 106

If you don't add a space in your XML query you will not get spaces in the middle. You can simple add empty string so that you don't get an XML back.

SELECT ID, Name = (SELECT ''+Name --Use empty string instead of space here
                     FROM [dbo].[a] AS a2
                     WHERE a2.ID = a.ID
                     ORDER BY ID
                     FOR XML PATH(''))
FROM [dbo].[a] AS a
GROUP BY ID ORDER BY ID;

Upvotes: 3

Related Questions