Concatenate Strings - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Concatenate Strings

Author

Topic

Jhouston132
Starting Member

11 Posts

Posted - 2006-04-02 : 04:13:01

I'm trying to group recordsets based on their IDs. This recordset has two columns: customer_id, last_name

however I can't come up with a function to concatenate the last names. So if I had this:


customer_id   |   last_name
1                  brock
1                  parker
1                  cassidy
3                  osborne
4                  watson
4                  gordon

it would turn to this:


customer_id   |   last_name
1                  brock,parker,cassidy
3                  osborne
4                  watson,gordon

Any ideas? Someone mentioned cursors before, but I think it would run slow.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-02 : 04:26:40

see here http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

KH
Choice is an illusion, created between those with power, and those without.

Jhouston132
Starting Member

11 Posts

Posted - 2006-04-02 : 18:24:08

Ah, thanks for the solution! Hmm, what happens if the table is a temporary table or table variable?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-02 : 19:11:34

If you are using v2005 then a CTE should be able tro do it.
If not then you could copy into a permanent table with the spid as an identifier and use
http://www.nigelrivett.net/SQLTsql/CSVStringFromTableEntries.html
restricting the function by a passed in spid.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources