Is COALESCE the best way to achieve this?

sql, sql-server-2005

Solution

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

Problem

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

Original source