How to merge several records in one using COALESCE in MS SQL 2008

Question

I'm guering three tables from the DataBase with the idea to extract information for a certain Client so I get single values from all columns except one.

My tables are :

• Client :: (ClientId | ClientName)

• Notifications :: (NotificationId | NotificiationText)

• ClientsNotifications :: (ClientId | NotificationId)

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 COALESCE.

I made this query :

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

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.

So There are three things that I want but don't know how to do -

1. Right now for column name I get (No column name) so I want to give it one
2. I want the two notifications (or as many as they are) concatenated in a single row
3. I want to determine some delimeter so when I fetch the records I can perform split. In my example I use this - ';') which I think should act as delimeter but the concatenated strings that I have are not separeted by ; or anything.

Answer 1

You can give your column name an alias in the same way you do for a table, e.g.

SELECT  <expression> AS ColumnAlias

However, for reasons detailed here I prefer using:

SELECT ColumnAlias = <expression>

Then to get multiple rows into columns you can use SQL Servers XML extensions to achieve this:

SELECT  c.ClientName, 
        Notifications = STUFF(( SELECT  ';' + n.NotificationText
                                FROM    [MyDB].[dbo].[ClientsNotifications] AS cn 
                                        INNER JOIN [MyDB].[dbo].[Notifications] AS n
                                            ON n.NotificationId = cn.NotificationId
                                WHERE   c.ClientId = cn.ClientId
                                FOR XML PATH(''), TYPE
                            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM    [MyDB].[dbo].[Client] AS c
WHERE   c.ClientId = 1;

Example on SQL Fiddle

An explanation of how this method works can be found in this answer so I shalln't repeat it here.

Answer 2

There's a trick to doing what you want to do. As it's written right now, you're just grabbing stuff off the same row. Also, multiple conditions on the second left join are unnecessary.

DECLARE @clientName VARCHAR(MAX) = '';
DECLARE @text VARCHAR(MAX) = '';

SELECT @clientName = c.ClientName
     , @text = (CASE
                   WHEN n.NotificationText IS NOT NULL THEN @text + ';' + n.NotificationText
                   ELSE @text
                END)
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 cn.NotificationId = n.NotificationId
WHERE c.ClientId = 1

SELECT @clientName AS ClientName, @text AS Notifications