concat multiple rows into one string

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

Transact-SQL (2000)

concat multiple rows into one string

Author

Topic

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2003-04-02 : 15:22:08

I have a table with product codes as a column. I need to take these product codes and concatenate them into one string to insert into another table. Example:

Prod_Code
---------
2001
2002
2003
2004

I need to end up with '2001,2002,2003,2004'.

There are a variable number of product codes per person. How can I accomplish this without a cursor? Thanks for any help.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-02 : 15:30:42

~~Converting Multiple Rows into a CSV String~~

EDIT: what the hell am I doing? I should be pimping my own article!!!

Converting Multiple Rows into a CSV String (Set Based Method)

Jay White
{0}

Edited by - Page47 on 04/02/2003 15:46:25

Bambola
Posting Yak Master

103 Posts

Posted - 2003-04-02 : 15:37:09

declare @str varchar(8000)
select @str = COALESCE(@str + ', ','') + convert(varchar(4),my_year)
from Prod_Code

select @str

Bambola.

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2003-04-02 : 16:06:31

Thanks for the help. I created a user defined function out of the COALESCE example and it works perfectly for me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources