title: Performing string concatenation from rows of data in a TSQL view (pivot?)
body: <p>I'd like to create a view in SQL Server that combines several pieces of database metadata.</p>
  
  <p>One piece of metadata I want lives in the <code>sys.syscomments</code> table - the relevent columns are as follows:</p>
  
  <pre><code>id   colid  text
  ---- ------ -------------
  1001 1       A comment.
  1002 1       This is a lo
  1002 2       ng comment.
  1003 1       This is an e
  1003 2       ven longer c
  1003 3       omment!
  </code></pre>
  
  <p>As you can see the data in the "text" column is split into multiple rows if it passes the maximum length (8000 bytes/4000 characters in SQL Server, 12 characters in my example). <code>colid</code> identifies the order in which to assemble the text back together.</p>
  
  <p>I would like to make query/subquery in my view to reassemble the comments from the sys.syscomments table, so that I have:</p>
  
  <pre><code>id   comment (nvarchar(max))
  ---- ----------------------------------
  1001 A comment.
  1002 This is a long comment.
  1003 This is an even longer comment!
  </code></pre>
  
  <p>Any suggestions or solutions? Speed is not in any way critical, but simplicity and low impact is (I would like to avoid CLR functions and the like - ideally the whole thing would be wrapped up in the view definition). I have looked into some XML based suggestions, but the results have produced text filled with XML escape strings.</p>