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.
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
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
Take a look here: Concatenate Values From Multiple Rows Into One Column Ordered
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With