title: How to use Coalesce for string concatenation in a subquery?
body: <p>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. </p>
  
  <p>Something along the line of </p>
  
  <pre><code>Declare @assignTo nvarchar(4000)
  
  Select 
  table1.columnA
  table1.columnB
  (
  select @assignTo = Coalesce(@assignTo + ', ', '') + CAST(Name as nvarchar(250))
  from
  table2    
  where
  ...
  )
  from table1
  where
  </code></pre>
  
  <p>.....</p>
  
  <p>I keep getting "Incorrect syntax near '='."</p>
  
  <p>If I just try to execute the subquery where the Coalesce function is called, its' fine. i.e</p>
  
  <pre><code> Declare @assignTo nvarchar(4000) 
   select @assignTo = Coalesce(@assignTo + ', ', '') + CAST(Name as nvarchar(250))
      from
      table2    
      where
      ...
    Select @assignTo
  </code></pre>
  
  <p>That's fine. So my question is, how do I include it as a subquery? </p>
  
  <p>Thanks a lot</p>
  
  <p>ps: This is specific to SQL server 2000. </p>