How to use Coalesce for string concatenation in a subquery?

Question

I'm trying to string concatenate multiple row values from one table using "Coalesce", separated by comma and list it as a column in a subquery.

Something along the line of

Declare @assignTo nvarchar(4000)

Select 
table1.columnA
table1.columnB
(
select @assignTo = Coalesce(@assignTo + ', ', '') + CAST(Name as nvarchar(250))
from
table2    
where
...
)
from table1
where

.....

I keep getting "Incorrect syntax near '='."

If I just try to execute the subquery where the Coalesce function is called, its' fine. i.e

 Declare @assignTo nvarchar(4000) 
 select @assignTo = Coalesce(@assignTo + ', ', '') + CAST(Name as nvarchar(250))
    from
    table2    
    where
    ...
  Select @assignTo

That's fine. So my question is, how do I include it as a subquery?

Thanks a lot

ps: This is specific to SQL server 2000.

Answer 1

You can't include it as a subquery: you'll have to move it into a UDF.

In SQL Server 2005 you can with the XML PATH technique. But for SQL Server 2000 you have to have a separate scalar UDF with table access and the concatenation