Is COALESCE the best way to achieve this?

Question

Hi everyone,

Just want to see if anyone has a better way to accomplish what I need.

First a background. I have a table in my database that currently has about 20,000 rows. In the table we have a column for FirstName and LastName. There is also a column that is a ReferenceID. There can be multiple FirstName, LastName combinations for each ReferenceID.

What that means is when we do a select * from the table we get multiple rows back for each ReferenceID (since there is a row for each user).

I want to concatenate these two columns in a column called Name at the same time as taking each row for the ReferenceID and having it turned into one. (Basically flatten the data).

Let me try to draw an ascii diagram here.

TABLE
ID        ReferenceID        FirstName        LastName
________________________________________________
1         1                  Mike             Ekim
2         1                  Tom              Mot
3         1                  Pete             Etep
4         2                  Ryan             Nayr
5         3                  Jerry            Yrrej

So the end result of what I would like is a set such as

RESULT SET
ReferenceID   Name
__________________________________
1              Mike Ekim, Tom Mot, Pete Etep
2              Ryan Nayr
3              Jerry Yrrej

What I really need to know is before I start down a path with COALESCE and try pivoting my results is there a better way to accomplish this? Using SQL Server 2005.

Cheers,

Mike

Answer 1

Looks good to me. In the past, I've done similar things and found the COALESCE trick the most straightforward way to do it.

This is an often wanted feature that's hard to find for unless you know what you're looking for, so here's a nice article that describes in detail how to do it: http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string.

Answer 2

You can use FOR XML PATH to generate a comma separated list. See for example this blog post:

SELECT P.Name + ','
FROM Production.Product AS P
ORDER BY P.Name
FOR XML PATH('')

Answer 3

Another option is to use the CLR. You can create a custom user defined aggregate written in c# or VB.Net that would return a comma separated list (which would then be used exactly as you would use SUM, or COUNT).

See this page and this page to get started.

Answer 4

Here is how you do it with the xml path technique (there is a coalesce in there too...)

SELECT DISTINCT n.ReferenceID,
STUFF((SELECT ', ' + COALESCE(n2.FirstName+' '+n2.LastName,n2.FirstName,n2.LastName,'NoName') 
    FROM namelist n2
    WHERE n.referenceid = n2.referenceid
    ORDER BY n2.lastname, n2.firstname
    FOR XML PATH('')
  ), 1, 2, '') AS [Name]
FROM namelist n

Answer 5

This topic is covered in this article: Concatenating Row Values in Transact-SQL. There are several techniques (XML PATH, recursive CTEs, CLR, recursive UDFs, cursor based, variable concatenation) and each is presented briefly, and the comments and linked sources from the article cover the topic further.

My favourite technique is XML PATH (as Andomar already posted).