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 |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2002-01-17 : 15:07:34
|
I've got a table in the form:Create Table Prefs (prefID int, prefValue int)...There can be one, or more than one prefValue per prefID, so data looks like this:prefID prefValue1 12 23 13 24 35 15 2 5 3 I need to generate comma-delimited strings for each prefID. For the example data above, for instance, I need a result like:PrefID String1 12 23 1,24 35 1,2,3 ...I'm familiar with using COALESCE to get comma delimited strings from multiple result rows. However, I can't get it to do this.I see how it could be done with a cursor and subqueries pretty easily, but I'm hoping to avoid that.Any ideas?Thanks-b |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-01-17 : 20:40:56
|
| Hmm... definitely good solutions, but I was really hoping for something set-based rather than a loop, as the query may be called fairly often. I guess I could put a trigger on the table and rebuild a static table of id's and the delimited strings every time the original table is updated, but that seems kind of ugly, too.Any other thoughts?Thanks-b |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-17 : 21:04:33
|
quote:
Hmm... definitely good solutions, but I was really hoping for something set-based rather than a loop
Uhhhh, those ARE set based solutions. The fact is that you are consolidating a set of data into individual grouped rows. I don't know of another way to do it without a loop, except the dreaded CURSOR. Try it first, throw as much traffic at it as you can, then see if performance is slow; don't assume it will be until you test it thoroughly. You can also analyze the execution plan and look for bottlenecks and fix them. Using a trigger might not be so bad, except what to do if values are updated or deleted?This was recently posted and may give you another idea:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12153Also, if you can find Sybase SQL*Anywhere, they have a LIST operator that does this, but.... BTW, where is the CSV data going? If it's going to a client app, and you're using ADO to connect, you might try using GetString or another method to create the CSV on the client instead; it will be a lot faster. (no, it's not worth doing it in SQL Server, too much overhead) |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-01-19 : 13:42:17
|
| Thanks -- I will try it. I realize that inside the loop, it's set based, however the overall solution is somewhat procedural since it has to iterate. Still, I'll give it a shot.And I'm not about to go to SQL*Anywehre over this :)The data is going all over. This whole problem is basically an interim solution that will bridge two versions of my app; the old version was really stupid, and the list form of the data is used (as a string) throughout both the client (ADO), and the DB (yes, there are varchar fields stuffed with comma delimited strings). This problem, at least, I can blame someone else for (for once!).So in the process of moving over to properly normalized, referential design, I need to support a bunch of old code. Hence the mapping. One day, the comma delimited stuff will be gone altogether, but it's too big of a project to do in one fell swoop.Thanks again!-b |
|
|
|
ToddV
Posting Yak Master
218 Posts |
Posted - 2002-01-21 : 08:59:56
|
| If you have a limited number of prefValue, this is a valid solution:SELECT Prefid, SUBSTRING(Max(Case when prefValue = 1 THEN ',1' ELSE '' END ) + Max(Case when prefValue = 2 THEN ',2' ELSE '' END ) + Max(Case when prefValue = 3 THEN ',3' ELSE '' END ) ,2,10)FROM #TEMP GROUP BY Prefid |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-01-21 : 19:45:47
|
| Fantastic -- just what I needed. There are never more than 4 prefID's, so it's very manageable.Thanks!-b |
|
|
|
|
|
|
|
|
Comma-delimited string from group-by query?