title: How to merge several records in one using COALESCE in MS SQL 2008
body: <p>I'm guering three tables from the DataBase with the idea to extract information for a certain <code>Client</code> so I get single values from all columns except one. </p>
  
  <p>My tables are :</p>
  
  <ul>
  <li><p><code>Client :: (ClientId | ClientName)</code></p></li>
  <li><p><code>Notifications :: (NotificationId | NotificiationText)</code></p></li>
  <li><p><code>ClientsNotifications :: (ClientId | NotificationId)</code></p></li>
  </ul>
  
  <p>A single client may have multiple notifications related to him, but I want to get them in a single row so after little research I decied that I should use <code>COALESCE</code>. </p>
  
  <p>I made this query :</p>
  
  <pre><code>SELECT c.ClientName, (COALESCE(n.NotificiationText,'') + n.NotificiationText + ';')
  FROM [MyDB].[dbo].[Client] AS c
  LEFT JOIN [MyDB].[dbo].[ClientsNotifications] AS cn 
  ON c.ClientId = cn.ClientId
  LEFT JOIN [MyDB].[dbo].[Notifications] AS n
  ON c.ClientId = cn.ClientId
  AND cn.NotificationId = n.NotificationId
  WHERE c.ClientId = 1
  </code></pre>
  
  <p>For this particular user I have two notifications, the result I get is - two rows, on the first row I have the first notification concatenated for itself (I have two times the same string) on the second row I have the second notification concateneated for itself again.</p>
  
  <p>So There are three things that I want but don't know how to do -</p>
  
  <ol>
  <li>Right now for column name I get <code>(No column name)</code> so I want to give it one</li>
  <li>I want the two notifications (or as many as they are) concatenated in a single row</li>
  <li>I want to determine some delimeter so when I fetch the records I can perform <code>split</code>. In my example I use this - <code>';')</code> which I think should act as delimeter but the concatenated strings that I have are not separeted by <code>;</code> or anything.</li>
  </ol>