Performing string concatenation from rows of data in a TSQL view (pivot?)

Question

I'd like to create a view in SQL Server that combines several pieces of database metadata.

One piece of metadata I want lives in the sys.syscomments table - the relevent columns are as follows:

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!

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). colid identifies the order in which to assemble the text back together.

I would like to make query/subquery in my view to reassemble the comments from the sys.syscomments table, so that I have:

id   comment (nvarchar(max))
---- ----------------------------------
1001 A comment.
1002 This is a long comment.
1003 This is an even longer comment!

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.

Answer 1

SELECT  id,
        (
        SELECT  text AS [text()]
        FROM    mytable mi
        WHERE   mi.id = md.id
        ORDER BY
                mi.col
        FOR XML PATH(''), TYPE
        ).value('/', 'NVARCHAR(MAX)')
FROM    (
        SELECT  DISTINCT id
        FROM    mytable
        ) md