SQL Server 2000: Ideas for performing concatenation aggregation subquery

Question

i have a query that returns rows that i want, e.g.

QuestionID  QuestionTitle  UpVotes  DownVotes  
==========  =============  =======  =========  
2142075     Win32: Cre...        0          0  
2232727     Win32: How...        2          0  
1870139     Wondows Ae...       12          0  

Now i want to have a column returned, that contains a comma separated list of "Authors" (e.g. original poster and editors). e.g.:

QuestionID  QuestionTitle  UpVotes  DownVotes  Authors
==========  =============  =======  =========  ==========
2142075     Win32: Cre...        0          0  Ian Boyd  
2232727     Win32: How...        2          0  Ian Boyd, roygbiv
1870139     Wondows Ae...       12          0  Ian Boyd, Aaron Klotz, Jason Diller, danbystrom

Faking It

SQL Server 2000 does not have a CONCAT(AuthorName, ', ') aggregation operation, i've been faking it - performing simple sub-selects for the TOP 1 author, and the author count.

QuestionID  QuestionTitle  UpVotes  DownVotes  FirstAuthor  AuthorCount  
==========  =============  =======  =========  ===========  =========== 
2142075     Win32: Cre...        0          0  Ian Boyd               1 
2232727     Win32: How...        2          0  Ian Boyd               2
1870139     Wondows Ae...       12          0  Ian Boyd               3

If there is more than one author, then i show the user an ellipses ("…"), to indicate there is more than one. e.g. the user would see:

QuestionID  QuestionTitle  UpVotes  DownVotes  Authors
==========  =============  =======  =========  ==========
2142075     Win32: Cre...        0          0  Ian Boyd
2232727     Win32: How...        2          0  Ian Boyd, …
1870139     Wondows Ae...       12          0  Ian Boyd, …

And that works well enough, since normally a question isn't edited - which means i'm supporting the 99% case perfectly, and the 1% case only half-assed as well.

Threaded Re-query

As a more complicated, and bug-prone solution, i was thinking of iterating the displayed list, and spinning up a thread-pool worker thread for each "question" in the list, perform a query against the database to get the list of authors, then aggregating the list in memory. This would mean that the list fills first in the (native) application. Then i issue a few thousand individual queries afterwards.

But that would be horribly, horrendously, terribly, slow. Not to mention bug-riddled, since it will be thread work.

Yeah yeah yeah

Adam Mechanic says quite plainly:

Don't concatenate rows into delimited strings in SQL Server. Do it client side.

Tell me how, and i'll do it.

/cry

Can anyone think of a better solution, that is as fast (say...within an order of magnitude) than my original "TOP 1 plus ellipses" solution?

For example, is there a way to return a results set, where reach row has an associated results set? So for each "master" row, i could get at a "detail" results set that contains the list.

Code for best answer

Cade's link to Adam Machanic's solution i like the best. A user-defined function, that seems to operate via magic:

CREATE FUNCTION dbo.ConcatAuthors(@QuestionID int)
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @Output VARCHAR(8000)
    SET @Output = ''

    SELECT @Output =    CASE @Output 
                WHEN '' THEN AuthorName 
                ELSE @Output + ', ' + AuthorName 
                END
    FROM  (
        SELECT QuestionID, AuthorName, QuestionDate AS AuthorDate FROM Questions
        UNION
        SELECT QuestionID, EditorName, EditDate FROM QuestionEdits
    ) dt
    WHERE dt.QuestionID = @QuestionID
    ORDER BY AuthorDate

    RETURN @Output
END

With a T-SQL usage of:

SELECT QuestionID, QuestionTitle, UpVotes, DownVotes, dbo.ConcatAuthors(AuthorID)
FROM Questions

Answer 1

Have a look at these articles:

http://dataeducation.com/rowset-string-concatenation-which-method-is-best/

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ (See Phil Factor's cross join solution in the responses - which will work in SQL Server 2000)

Obviously in SQL Server 2005, the FOR XML trick is easiest, most flexible and generally most performant.

As far as returning a rowset for each row, if you still want to do that for some reason, you can do that in a stored procedure, but the client will need to consume all the rows in the first rowset and then go to the next rowset and associate it with the first row in the first rowset, etc. Your SP would need to open a cursor on the same set it returned as the first rowset and run multiple selects in sequence to generate all the child rowsets. It's a technique I've done, but only where ALL the data actually was needed (for instance, in a fully-populated tree view).

And regardless of what people say, doing it client-side is often a very big waste of bandwidth, because returning all the rows and doing the looping and breaking in the client side means that huge number of identical columns are being transferred at the start of each row just to get the changing column at the end of the row.

Wherever you do it, it should be an informed decision based on your use case.