String Concatenation from one field

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)

String Concatenation from one field

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2006-01-30 : 00:55:24

I dont know if it is possible, and I have not found any other examples of posts trying to do what I am doing. I have a table that holds all rooms assigned for an event.

EventID------Resource
20----------BallRoom
20----------Terrace
20----------Mixed Grille
21----------BallRoom
21----------Lounge
22----------Mixed Grille

What I need to return is one string field containing all the rooms assigned for an event.

So if I do something like
Select Resource from EventResource where EventID = 20

I need the results to be
Ballroom, Lounge, Mixed Grille

But I cannot find anywhere if this is even possible. Thoughts or suggestions?

Thanks so much,
JAdauto

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-30 : 01:07:42

Do this way..
declare @resource varchar(800)
Select @resource = coalesce(@resource + ',' + resource ,resource ) from EventResource where EventID = 20
select @resource

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-30 : 02:12:50

For multiple, ids refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

JAdauto
Posting Yak Master

160 Posts

Posted - 2006-01-31 : 00:28:41

Thanks to both of you. IN fact, I read that link prior to posting and somehow I could not get what it was saying. Anyway, that solution is working perfectly, except I need to add a wrench to it. I really simplified the data structure. There are a few tables actually involved as they can assign rooms to different time types within an event. So they may have cocktails in one room, the meal in another, the reception back in the first room. Long story short, I need to add a Distinct in that somewhere so that I only get the room name once. But everywhere I try to add it, I get a syntax error. Suggestions?

Thanks again

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-31 : 00:57:09

Try this..
declare @resource varchar(800)
Select @resource = coalesce(@resource + ',' + resource , resource )
from ( Select distinct resource from EventReSource where EventID = 20 ) d
select @resource

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-31 : 01:06:55

Yes, using derived table is the only way in this case

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources