Select with COALESCE built list in query

Question

Possible Duplicate:
Simulating group_concat MySQL function in MS SQL Server 2005?

I have a query that needs to return a set of fields, along with a list of data returned as a single character-separated string.

Say I have 2 tables

tblPeople

NamePK    Name
-----------------
1         Fred
2         Tom

tblNotes

NameFK    Note
------------------------
1         Likes to talk
1         Types fast
2         Joined the internet

Then my answer would look like this (if I separated with a '+')

NamePK    Name    Notes
-------------------------------------------
1         Fred    Likes to talk+Types fast
2         Tom     Joined the internet

I believe I have to use the COALESCE function. I have used it in the past to create comma-delimited values, but I am unsure on how to do this in a select. I'm using MSSQL.

Any ideas or approaches?

suggestions: Sql COALESCE entire rows

What version of SQL Server?. Anyway, this group concatenation thing has been asked many, many times before. I recommend you to do a search on SO with the tags `sql-server` and `group-concat` — Lamak, Jun 28 '12 at 16:28
The people I work for are crazy. The end result sort of makes sense, but I understand that this is not common SQL practice. If you can think of a better way to format this, please suggest. — gunr2171, Jun 28 '12 at 16:29
[Here's a stackoverflow question that answers what you are looking for](http://stackoverflow.com/questions/3368942/grouped-string-aggregation-listagg-for-sql-server) — N West, Jun 28 '12 at 16:31
I know i can concat using COALESCE. [link](http://www.mssqltips.com/sqlservertip/1521/the-many-uses-of-coalesce-in-sql-server/) — gunr2171, Jun 28 '12 at 16:31
@gunr2171 the `COALESCE` function does *not* concatenate. It simply returns the first argument to the function that is `NOT NULL`. You concatenate columns using the `+` operator in SQL Server. — N West, Jun 28 '12 at 16:33
@gunr2171 - Your link shows coalesce being used to deal with nulls to allow a concatentation. It does not show coleasce being use to do the concatenation. It especially has nothing to do with concatention over multiple rows. *[I strongly suggest looking at the answsers linked to above, they show methods for achieving what you have asked for.]* — MatBailie, Jun 28 '12 at 16:33
@nwest - Except that in the OPs case you need to pivot to an unknown number of columns. Which isn't so easy. — MatBailie, Jun 28 '12 at 16:35
@Dems, There is only one column I need to pivot. And thanks to everyone's suggestions so far. — gunr2171, Jun 28 '12 at 16:37

score 2 · Accepted Answer · edited May 23 '17 at 12:16

Use STUFF and FOR XML PATH to do the concatenation. See this question for an example. There are improvements that can be made, such as eliminating extra delimiters, if you search around for other questions involving STUFF.

SELECT p.NamePK, p.Name,
 STUFF((
        select '+' + n.Note
        from tblNote n
        where n.NameFK = p.NamePK
        for xml path('')
    ), 1, 1, '')
FROM tblPeople p

Select with COALESCE built list in query

1 Answers1