title: SQL Server 2000: Ideas for performing concatenation aggregation subquery
body: <p>i have a query that returns rows that i want, e.g.</p>
  
  <pre><code>QuestionID  QuestionTitle  UpVotes  DownVotes  
  ==========  =============  =======  =========  
  2142075     Win32: Cre...        0          0  
  2232727     Win32: How...        2          0  
  1870139     Wondows Ae...       12          0  
  </code></pre>
  
  <p>Now i want to have a <strong>column</strong> returned, that contains a comma separated list of "<em>Authors</em>" (e.g. original poster and editors). e.g.:</p>
  
  <pre><code>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
  </code></pre>
  
  <p><br></p>
  
  <h2>Faking It</h2>
  
  <p>SQL Server 2000 does not have a <code>CONCAT(AuthorName, ', ')</code> aggregation operation, i've been faking it - performing simple sub-selects for the <code>TOP 1</code> author, and the author count.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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, …
  </code></pre>
  
  <p>And that works well enough, since normally a <em>question</em> isn't edited - which means i'm supporting the 99% case perfectly, and the 1% case only half<strike>-assed</strike> as well.</p>
  
  <p><br></p>
  
  <h2>Threaded Re-query</h2>
  
  <p>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 "<em>question</em>" 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.  </p>
  
  <p>But that would be horribly, horrendously, terribly, slow. Not to mention bug-riddled, since it will be thread work.</p>
  
  <p><br></p>
  
  <h2>Yeah yeah yeah</h2>
  
  <p>Adam Mechanic <a href="http://dataeducation.com/rowset-string-concatenation-which-method-is-best/" rel="nofollow noreferrer">says quite plainly</a>:</p>
  
  <blockquote>
    <p>Don't concatenate rows into delimited
    strings in SQL Server. Do it client
    side.</p>
  </blockquote>
  
  <p>Tell me how, and i'll do it.</p>
  
  <p><br></p>
  
  <h2>/cry</h2>
  
  <p>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?</p>
  
  <p>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.</p>
  
  <p><br></p>
  
  <h2>Code for best answer</h2>
  
  <p><a href="https://stackoverflow.com/questions/2410474/sql-server-2000-ideas-for-performing-concatenation-aggregation-subquery/2410524#2410524">Cade's</a> link to <a href="http://dataeducation.com/rowset-string-concatenation-which-method-is-best/" rel="nofollow noreferrer">Adam Machanic's solution</a> i like the best. A user-defined function, that seems to operate via magic:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>With a T-SQL usage of:</p>
  
  <pre><code>SELECT QuestionID, QuestionTitle, UpVotes, DownVotes, dbo.ConcatAuthors(AuthorID)
  FROM Questions
  </code></pre>