Select query get result comma seperated

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 2005 Forums

Transact-SQL (2005)

Select query get result comma seperated

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-03-31 : 11:15:51

I have a table tab_categories, has field cat_name

want to get all cat_name as a comma seperated result.

is it possible.

Thanks for teh helpful info.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 11:31:05

Yes it is possible. Do you want comma separated list in variable or in an output?

Harsh Athalye
http://www.letsgeek.net/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-03-31 : 11:50:03

As an output or variable too fine...

Thanks.

quote:
Originally posted by harsh_athalye

Yes it is possible. Do you want comma separated list in variable or in an output?

Harsh Athalye
http://www.letsgeek.net/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 11:54:44

[code]
Declare @result varchar(8000)

select @result = coalesce(@result + ',', '') + cat_name
from tab_categories

select @result[/code]

Harsh Athalye
http://www.letsgeek.net/

PackRat
Starting Member

26 Posts

Posted - 2010-03-31 : 13:00:40

quote:
Originally posted by harsh_athalye

Declare @result varchar(8000)

select @result = coalesce(@result + ',', '') + cat_name
from tab_categories

select @result

Harsh Athalye
http://www.letsgeek.net/

Nice trick with the coalesce and the coma!
Hadn't thought of that one before, I've usually gone with a couple extra steps.

My only meaningful addition is to NULL protect the column value so you don't wipe out your string with a NULL value.


Declare @result varchar(8000);
SET @result='';

SELECT @result = @result + ISNULL(cat_name + ',', '')
FROM tab_categories
-- or WHERE cat_name IS NOT NULL

SET @result=LEFT(@result, LEN(@result)-1);

SELECT @result

_____________________________
wrote this on my TRS-80 COCO4

<PakRat/>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources