| Author |
Topic |
|
iansr
Starting Member
23 Posts |
 Posted - 2003-01-15 : 21:58:51
|
| Greetings!Im using SQL Server 7.0 (Service Pack 4)I was wondering if there is a way to aggregate a column in the select list. i.e.***select firstname, 'groupName' = ( select STRINGAGGREGATE(groupName, ', ') from groups where userID = A.userID )from users A***This will return:firstname groupName-----------------------------------------Ian Admin, Group1John Group1Jonathan Group2Id like to avoid putting this into another stored proc altogether, and calling it for every user.Any suggestion will be appreciated.Thanks!Ian |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-01-16 : 01:33:59
|
| Well, looks like you're building a CSV list from a number of rows. One command you might look into is COALESCE. But really, you can probably save yourself a lot of effort by clicking on this search: [url]http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv[/url] and read the numerous articles on our site about CSV's.------------------------------------------------------The more you know, the more you know you don't know. |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-01-16 : 07:45:19
|
Look at the solutions given at:http://www.sqlteam.com/item.asp?ItemID=11021Unfortunately, you don't have SQL 2000 which allows for a really efficent User-defined function method to handle this. I really wish SQL had an CONCAT() aggregate function .....I wouldn't expect someone who is asking "is there a way to concatenate strings in SQL" to know to search for the letters "CSV", IMHO. What if the delimiter is a semicolon? Do they search for "SSV"?  - Jeff |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-16 : 10:43:28
|
quote:
Unfortunately, you don't have SQL 2000 which allows for a really efficent User-defined function method to handle this.
Not unfortunate at all, you don't need to use a function to do this. In addition to the links you provided are these:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=14095http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=15651http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9978Some of these methods are 8 lines of code and would fit nicely into a stored procedure. They are at the very WORST just as efficient as a function would be, and probably are far more efficient because they avoid the additional overhead of a function call, and they can work on an entire set of data. A UDF to do this would become a row-based operation.quote:
I really wish SQL had an CONCAT() aggregate function
Write one for yourself, or use a stored procedure, there are about 8 different varieties listed in the links provided in this thread.  quote:
I wouldn't expect someone who is asking "is there a way to concatenate strings in SQL" to know to search for the letters "CSV", IMHO. What if the delimiter is a semicolon? Do they search for "SSV"?
You're right, although when I searched for "build string" I got most of the articles listed in CSV. The links I found in the forum used "string sum" as a search term.Yep, had to fix this just a little smidgen... Edited by - robvolk on 01/16/2003 11:10:31 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-01-16 : 10:54:11
|
quote:
They are at the very WORST just as efficient as a function would be, and probably are far more efficient because they avoid the additional overhead of a function call, and they can work on an entire set of data.
I think the most applicable method for iansr is the approach I outlined in this article. However, if you read David's comments, you'll see that his function outperforms my method.Jay White{0} |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-01-16 : 12:11:08
|
quote:
Write one for yourself, or use a stored procedure, there are about 8 different varieties listed in the links provided in this thread.
I couldn't find the info on BOL on how to create SQL aggregrate functions .... can you provide some guidance on that?- Jeff |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-16 : 12:12:28
|
| CREATE FUNCTION |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-01-16 : 12:21:03
|
| Wow, someone's having a bad afternoon !The key word was aggregrate.You know,SELECT ID, CustomFunction(Field) as NewFieldFROM TableGROUP BY IDCan you show an example?- Jeff |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-16 : 12:36:45
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19647It's a function that generates a CSV from the parameters you pass. It may not fit the description of "aggregate", but it does the job. To make it an aggregate function would require parsing the SQL statement to include the effects of the WHERE clause, the HAVING clause, any CASE expressions that might affect the outcome, etc.I'm not having a bad day Jeff, but I'm reading of lot of your posts as diatribes against the limitations of SQL Server, about things that are not built-in features but can be done (easily) with a little work on the part of people who need them. If that's not what your intention is, you need to more clear about that in your responses. There's lots to criticize about the product but ultimately it doesn't help anyone to use it. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-01-16 : 12:44:42
|
| Sorry I was so hurtful when I said it'd be nice to have an aggregate function of CONCAT.Didn't realize the harm I had done. Sorry I also said I thought it would be nice if you could change a UDT on the fly. just take a quick poll and if people feel I haven't been helpful and contributed solutions no one has thought of (i think i have just a little) and only insult SQL then fine, let me know and I'll be on my way.Glad to see, though, you apologized for your abrupt and incorrect reply to my question after insulting me as well. I'm personally really disapointed you didn't understand the difference between a function and an aggregate function. A huge, key difference and a fundamental thing to know about SQL.If you seriously didn't think I knew how to create a function or how to do a one-time stored proc or UDF as a work-around, then ...oh well, never mind. i guess I only have 300 posts so what do I know about SQL....- Jeff |
|
|
|
|
String Aggregates? (or alternatives)