Multiple rows in one column

[Zippeh]

Slightly odd question here...

I have three tables, a USER table, a CENTRE table and a link table on user_id and centre_id.

What I want to do is use ASP to print out the users along with the centres they work in. So i can have something like:

Fred Centre1, Centre2, Centre3
Joe Centre1, Centre3
Bob Centre4

Just wondering if there is some SQL that would allow me to have one column with all the centres in a comma separated list within that column to spare me doing it in the ASP?

 

[Catadmin]

Sure, use concatenation.

You may have to play with this a little, but I believe it should work.

Select u.User, (c.Centre1 + ', ' + c.Centre2 + ', ' + c.Centre3) as AllCentres 
from User u inner join Link l on u.UserId = l.UserID 
inner join Centre c on l.centre_ID = c.centre_ID

Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[vongrunt]

I guess three tables are normalized this way:

USER
user_ID
user_Name

CENTRE
centre_ID
centre_Name

USER_CENTRE
user_ID
centre_ID

Is that correct?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Zippeh]

Yeah that's correct. I want a comma separated list of all Centre_name (s) for an individual.

 

[Zippeh]

Catadmin - I don't believe what you have given me there will work. Sorry if the question wasn't clear. I don't have columns named "Centre1", "Centre2", "Centre3" I was going for these being the names of the centre.

The schema shown by vongrunt is how it is.

 

[Zippeh]

No one?

 

[JamesLean]

You could write a function that took a user id as input and returned the comma separated list of all that user's centres. But to be honest it is probably going to be easier and more efficient to return a simple query with one centre per row and then process and display the data in your ASP code.

--James

 

[Zippeh]

Thanks

If there isn't a nice easy was in SQL then I'll do it in ASP.