title: Sql Query to join table and coalesce values
body: <p>I'm a newbie to sql and am trying to to join two tables and coalesce values.
  the Coalesce code below works when run individually </p>
  
  <pre><code>Select  @ContactName = COALESCE(@ContactName + ', ', '') + c.ContactName from contacts  c inner join  functions b on c.pid= b.pid
  </code></pre>
  
  <p>but when i combine the whole code its not executing. Could someone advise be on the below.</p>
  
  <pre><code> select 
  a.*,
  b.tfunction, 
  b.SSummary,
  b.ContactType,
  (  Select  @ContactName = COALESCE(@ContactName + ', ', '') + c.ContactName from contacts  c inner join  functions b on c.pid = b.pid),
  (   Select  @Address = COALESCE(@Address + ', ', '') + c.Address from contacts  c inner join  functions b on c.pid = b.pid),
  (  Select  @ContactPhone = COALESCE(@ContactPhone + ', ', '') + c.ContactPhone from contacts  c inner join  functions b on c.pid = b.pid),
  (    Select  @ContactEmail = COALESCE(@ContactEmail + ', ', '') + c.ContactEmail from contacts  c inner join  functions b on c.pid = b.pid)
  from contracts a
  inner join functions b on a.pid = b.pid 
  </code></pre>
  
  <p>ERROR details</p>
  
  <pre><code>Msg 102, Level 15, State 1, Line 12
  Incorrect syntax near '='.
  Msg 102, Level 15, State 1, Line 13
  Incorrect syntax near ','.
  Msg 102, Level 15, State 1, Line 14
  Incorrect syntax near ','.
  Msg 102, Level 15, State 1, Line 15
  Incorrect syntax near ')'.
  </code></pre>
  
  <p>ps:- feel free to modify the code.</p>
  
  <p>PS- i have the declare variable for all the 4 attributes - Stack overflow is not allowing me to paste them for some reason.</p>