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

There are several possible solutions. The simplest one is to use CTE. This article has good discussion about the topic: Concatenating Row Values in Transact-SQL

Answer 2

Take a look here: Concatenate Values From Multiple Rows Into One Column Ordered

Answer 3

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

Answer 4

Why not use sys.sql_modules definition columns which stores the data as a single nvarchar(max)?

Instead of reading several syscomments text (nvarchar(4000)) columns spread over several rows and having to concatenate.

SELECT object_id, definition FROM sys.sql_modules

Just a thought... you did say simple :-)