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.
| Author |
Topic |
|
joshb
Yak Posting Veteran
52 Posts |
 Posted - 2002-06-20 : 16:57:49
|
| I have 2 tables with a one to many relationship between them. How can I write a select query which would produce the following result:OneTableField ManyTableFieldvalue_1 value_1, value_2, value_3value_2 value_4, value_5value_3 value_6, value_7Basically for each row I want a field with a value from the OneTable then another field listing the related values from the ManyTable in a comma seperated list.I have some ideas on how to do this with cursors but would rather not use them if I can avoid it.Any ideas? |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-06-20 : 17:49:32
|
| This is not directed at anyone in particular, but newbies are esp. guilty of this.<rant>Please search through some of the really great articles on the site before posting! </rant>Josh,goto http://www.sqlteam.com/Search.asptype in csv in the search box, and hit search. Your answer awaits.Michael |
 |
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
|
|
joshb
Yak Posting Veteran
52 Posts |
Posted - 2002-06-21 : 10:34:58
|
If anyone is interested I solved this one using the COALESCE method explained by Garth in his article "Using COALESCE to Build Comma-Delimited String". His article explains how to produce a list for a single OneTable value. I wrapped this in a UDF then used the UDF in a correlated subquery passing in the OneTable ID from the outer query and using it as a parameter for the UDF (the inner query). Once again UDFs save the day!Example:SELECT OneTable.ID, (SELECT dbo.fnCSV(OneTable.ID)) AS ManyListFROM OneTableIt performs pretty well too!Thanks to MichaelP and VyasKN for pointing me in the right direction |
|
|
|
|
|
|
tricky query (atleast for me)