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 |
|
ngjarman
3 Posts |
 Posted - 2008-04-22 : 10:35:06
|
| Hi, As I build a record set in an SP I need to add in a string containing a list derived from a second query. I need the results of the sub query to be presented as a single string in the first query, separated by a single space.I have no idea how to do this in t-sql, and am doing it on the web server at the moment, but becase the dataset is quite large, I'm getting 20 - 40 second processing times which is far too long.I have found reference to the xp_sprintf function but this is not supported by my host, so not a solution.I'm no expert in t-sql, so I imagine there's a way somewhere, and would be grateful for any advice available.regards,NEILNeil |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-22 : 10:38:57
|
Do you have any examples?Your request is very much like this"Can you paint my sideboard with the color in the bucket to the right of you."Now, what color is it?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-22 : 10:39:03
|
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
E 12°55'05.25"N 56°04'39.16" |
|
|
|
ngjarman
3 Posts |
Posted - 2008-04-22 : 11:48:19
|
| Sorry, I though my explaination was quite simple without being specific.Obviously I don't have an error becuase my code works (but is very slow) but here's the (simplified) query generating the outer result:SELECT TimeTableID, sTable, iStock from tblMetroTimeTableThen for each TimeTableID in the result, I need to get a list of routes (each timetable can contain one or more routes and one route might be in more than one timeteble)I'm currently iterating throught the entire list of routes (SELECT TimeTableID, sRouteCode FROM tblMetroRoute) and where the TimeTableID matches, I'm concatenating a string, ready to post to HTML when it's done.Clearly this is slow because it's happening on a web server, which is doing it's best, but there are in excess of 200 timetables and over 500 routes so it's pretty busy!!Hope this makes sense?Neil |
|
|
|
ngjarman
3 Posts |
Posted - 2008-04-22 : 13:13:51
|
| I found a great solution from this reference - [url]www.projectdmx.com/tsql/rowconcatenate.aspx [/url]Well done to the guys who wrote it!!Neil |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
|
|
concatenate string in query