Concatenating rows from data rows in TSQL view (pivot?)

Question

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

One piece of metadata that I want to live in the sys.syscomments table is the corresponding columns:

 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 divided into several rows if it skips the maximum length (8000 bytes / 4000 characters in SQL Server, 12 characters in my example). colid identifies the assembly order of the text back.

I would like to make a query / subquery in my opinion to collect comments from the sys.syscomments table, so 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 by no means critical, but simplicity and low impact (I would like to avoid CLR functions and the like - ideally, all this would be wrapped in the definition of a view). I looked at some XML-based suggestions, but as a result, you got text filled with lines of XML escape code.

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 

Answer 2

There are several possible solutions. The easiest is to use CTE. This article has a good discussion of the topic: Combining row values ​​in Transact-SQL

Answer 3

Take a look here: Merge values ​​from multiple rows into one column ordered

Answer 4

Why not use sys.sql_modules definition columns that store data as one nvarchar (max)?

Instead of reading multiple text lines, syscomments (nvarchar (4000)) are sent across multiple lines and must be concatenated.

 SELECT object_id, definition FROM sys.sql_modules 

Just a thought ... you said simply :-)