SQL 'STUFF' statement advice

I needed to select all rows from a table matching a requirement, but to put these into one column seperated by a space/comma. Right now, I have this amongst my query:

  ((SELECT  ' ' + ID
   FROM Items
    WHERE     (Consignment = Consignments.ConsignmentNo) FOR XML PATH('')), 1, 1, '') AS Items

Problem is, it doesn't seperate the results by anything, so it all l开发者_开发技巧ooks like one result. Where am I going wrong?

Thanks

I am guessing your ID column is a numeric column rather than a varchar. Try casting ID as a varchar or nvarchar. Your syntax looks fine, it should seperate by a space.

EX:

Without the cast:

select 1 as Item
into #test
union select 2
union select 3
union select 4
union select 5

select STUFF((SELECT  ' ' + Item
        FROM #test
        FOR XML PATH('')), 1, 1, '')

Output: 2345

With the cast:

select 1 as Item
into #test
union select 2
union select 3
union select 4
union select 5

select STUFF((SELECT  ' ' + cast(Item as nvarchar)
        FROM #test
        FOR XML PATH('')), 1, 1, '')

Output: 1 2 3 4 5