SQL Concatenate ids into a specific order

Question

I have the following query which works great, it puts all my ids in a comma separated list.

DECLARE @tmp nvarchar(max)
SET @tmp = ''
        SELECT  @tmp = @tmp + cast(id as nvarchar(max)) + ', ' 
        FROM    dbo.property

I want to put my ids in alphabetical order but when I add order by p.Name it only gives my the top one result.

How can I adapt my query to accomplish this?

Answer 1

This really doesn't work?

DECLARE @tmp nvarchar(max);
SET @tmp = '';
SELECT  @tmp = @tmp + cast(id as nvarchar(max)) + ', ' 
FROM    dbo.property p
order by p.name;

In any case, you can use this method:

select @tmp = stuff((select ', ', cast(id as nvarchar(max))
                     from db.property p
                     order by p.name
                     for xml path ('')
                    ), 1, 2, '');

Answer 2

The solution:

    SELECT  (cast(ID as nvarchar(max)) + ', ')  AS [text()]
      FROM    dbo.property
  ORDER BY ID
    FOR XML PATH ('')