Could you write a special stored procedure for me in SQL 2005?

When I execute the SQL "select TagName from Th_TagTable where IDTopic=@.IDTopic" , it return several rows such as
TagName= http://www.hothelpdesk.com

TagName= http://www.hellocw.com
TagName= http://www.supercoolbookmark.com

I hope there is a stored procedure which can join all TagName into a single string and return,
it means when I pass @.IDTopic to the stored procedure, it return "http://www.hothelpdesk.com, http://www.hellocw.com, http://www.supercoolbookmark.com"

How can write the stored procedure?

The best answer to this question is in this post: http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

If you are using 2005, be sure and use the FOR XML PATH solution. It does not have of the issues that the 2000 solutions did, unless you used a cursor (which is equally heinous

Could you write a special stored procedure for me in SQL 2005?

Could you write a special stored procedure for me in SQL 2005?

When I execute the SQL "select TagName from Th_TagTable whereIDTopic=@.IDTopic" , it return several rows such as
TagName= Sport
TagName= New
TagName= Health

I hope there is a stored procedure which can join all TagName into a single string and return,
it means when I pass @.IDTopic to the stored procedure, it return "Sport,New,Health"

How can write the stored procedure?

Hi,

You can use the below t-sql stateents to get the desired list of tagnames. COALESCE is the key of the below script

DECLARE @.IdTopic int
SET @.IdTopic = 1

DECLARE @.str AS nvarchar(255)

SELECT
@.str = COALESCE(@.str + ', ', '') + tagname
FROM Th_TagTable (NoLock)
WHERE IdTopic = @.IdTopic

SELECT @.str

You can better create a user-defined function instead of a stored procedure to get similar values.

Check the article and sample athttp://kodyaz.com/articles/article.aspx?articleid=29 which gets the list of a book's authors.

Eralper

http://www.kodyaz.com

|||Thanks!