select muliple rows as comma separated values

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 muliple rows as comma separated values

Author

Topic

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2009-03-13 : 02:40:13

Hi,
i want to select multiple row values as comma separated values in one select statement for each item id
my table is like
tagid tagname itemid
1 aaa 1
2 bbb i

output shouldbe like
aaa,bbb
where itemid=1

thanks in advance,
de4ever

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-13 : 02:50:48

select stuff((select ','+tagname from tablename where itemid = t.itemid for xml path('')),1,1,'') from tablename t

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-13 : 03:36:09

Hey try this...

declare @s varchar(8000)

select @s = left(isnull(@s + ',', '') + x.name, 8000)
from (select name from mytable where autoid between 100 and 105) as x

select @s

Willing to update...

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2009-03-13 : 04:51:08

quote:
Originally posted by senthil_nagore

Hey try this...

declare @s varchar(8000)

select @s = left(isnull(@s + ',', '') + x.name, 8000)
from (select name from mytable where autoid between 100 and 105) as x

select @s

Willing to update...

but i want in single query

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2009-03-13 : 04:52:28

quote:
Originally posted by bklr

select stuff((select ','+tagname from tablename where itemid = t.itemid for xml path('')),1,1,'') from tablename t

this query is ok for me but will it degrade preformance , coz i am using it in my proc where i am joining 5 tables

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-13 : 05:13:28

Better U go for SP for your task...

The Performance is relatively good while joining multiple table!

Regards

Senthil.C
Willing to update...

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-13 : 05:36:30

quote:
Originally posted by de4ever@gmail.com

quote:
Originally posted by bklr

select stuff((select ','+tagname from tablename where itemid = t.itemid for xml path('')),1,1,'') from tablename t

this query is ok for me but will it degrade preformance , coz i am using it in my proc where i am joining 5 tables

the performance will be good if u use the xml path function in sp also

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2009-03-13 : 05:49:12

quote:
Originally posted by bklr

quote:
Originally posted by de4ever@gmail.com

quote:
Originally posted by bklr

select stuff((select ','+tagname from tablename where itemid = t.itemid for xml path('')),1,1,'') from tablename t

this query is ok for me but will it degrade preformance , coz i am using it in my proc where i am joining 5 tables

the performance will be good if u use the xml path function in sp also

Thanks a lot

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-13 : 06:03:01

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources