[philmwebb]

I'm working on an aggregate query where I'd like to SUM not only a numeric field for grouped records, but also concatenate the values from a string field (with some separator like ', ') If I weren't using Access I know I could write a stored proc for this, but surely there's a standard SQL way of doing this?

The kind of thing I guess I'm after is something like:

SELECT TaskID, SUM(Days) as TotalEffort, STRSUM(WorkerName, ', ') as Workers From Effort GROUP BY TaskID

So that records like:

ID, TaskID, Days, WorkerName
1, 1, 3, Phil
2, 1, 4, Phil
3, 1, 1, John
4, 2, 2, Mike
5, 2, 1, Mike
6, 2, 2, John

would give:

TaskID, TotalEffort, Workers
1, 8, 'Phil, John'
2, 5, 'Mike, John'

Any clues?

[andrewst]

No, if you can't write your own function to do it, and if Access doesn't have it built in, then it can't be done. It isn't a "standard" function in the way that SUM is, because SUM is unambiguous and frequently required. By contrast, the output of your STRSUM is ambiguous: should it be 'Phil, John' or 'John, Phil'? If there are 2 'Phil's, should it return 'Phil, Phil' or just 'Phil'? A standard function would have to take additional parameters to tweak all these behaviours.

[Pat Phelan]

There are engine specific functions that do what you are describing, but there isn't anything that I know of in the SQL standard that will do it. I know that Sybase SQL Anywhere, and PostgreSQL both include functions that will do this.

It isn't hard to write a user defined function that will handle a specific case, as long as your SQL dialect supports user defined functions. It is more of a challenge to do it generically.

This kind of processing is really better done on the client side. There are a number of problems that you can encounter if you try to do this on the server side, which is probably why the X3H2 SQL standards committe refused to take any position on the issue.

-PatP