How to use Coalesce for string concatenation in a subquery?

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 subquer开发者_StackOverflow社区y 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.

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

As far as I know, You can do like this if u prefer to do as SubQuery. But the above solution is the most convenient one.

Declare @assignTo nvarchar(4000)

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

Hope it works!