Concatenating results - 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)

Concatenating results

Author

Topic

toddhd
Yak Posting Veteran

55 Posts

Posted - 2006-01-11 : 11:34:34

Here is simplified example of what I want to do. Let's assume I have a simple table that contains a list of product names.

ProductID int
ProductName varchar(50)

I also have a Keyword table. These keywords relate to a given product by using the ProductID. Pretty typical PK/FK stuff.

KeywordID int
KeywordName varchar(15)
ProductID int

I can get all the related keywords by using a JOIN statement - no problem.

OK, so here is my question - what I'd LIKE to return is a formatted list of keywords. For example, instead of returning three records that say:
Keyword1
Keyword2
Keyword3

I want to return:
Keyword1, Keyword2, Keyword3

Is this possible? Is it feasible? Or would it make a lot more sense to just grab all the keywords and handle the concatenation in the program code instead?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-11 : 11:59:20

It does make sense to perform the concatenation in your presentation layer. However, here is a sql trick to do it. you can also put this logic in a function that will return a CSV for multiple rows:

declare @keywords varchar(50)

select @keywords = coalesce(@keywords + ', ' + keyword, keyword)
from	(
	select 'Keyword1' keyword union
	select 'Keyword2' union
	select 'Keyword3'
	) a

select @keywords [CSV]

results:

CSV                                                
-------------------------------------------------- 
Keyword1, Keyword2, Keyword3

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources