Concatenate columns strings for matching rows

Question

Here is my query:

SELECT 
    sys.objects.object_id, sys.objects.name, sys.objects.type_desc, 
    sys.syscomments.text
FROM 
    sys.objects
LEFT JOIN 
    sys.syscomments ON sys.syscomments.id = sys.objects.object_id
WHERE 
    (type_desc LIKE 'SQL_STORED_PROCEDURE' OR type_desc LIKE 'SQL_SCALAR_FUNCTION')
ORDER BY 
    sys.objects.object_id;

Here is part of the output:

So how would I concatenate the 'text' columns for rows with the same 'object_id' so that I don't have duplicates ID? Not so simple in T-SQL without an aggregate function for strings...

I've seen other examples but I can't seem to get it working for my scenario.

Show us sample data as text *( we cant copy/paste from pictures)* and desire output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. — Juan Carlos Oropeza, May 16 '16 at 19:09
Possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) — Tab Alleman, May 16 '16 at 19:19

score 1 · Answer 1 · answered May 16 '16 at 20:10

You need to create a function to concatenation:

CREATE FUNCTION [dbo].[GetTexts](@id INT)
    RETURNS VARCHAR(4000)
AS
BEGIN
    DECLARE @res NVARCHAR(4000)

    SELECT  DISTINCT
            @res =
            SUBSTRING(
            (
                SELECT  ',' + SC1.[text]
                FROM    dbo.syscomments SC1
                WHERE   SC1.id = SC2.id
                FOR     XML PATH ('')
            ), 2, 4000)
    FROM    dbo.syscomments SC2
    WHERE   SC2.id = @id

    RETURN @res
END

Then, execute the SELECT query.

SELECT  dbo.[objects].[object_id], dbo.[objects].name, dbo.[objects].type_desc, dbo.GetTexts([object_id])
FROM    dbo.[objects]

P.S.: I'm not sure about the schema names, so correct them as you want.

score 0 · Accepted Answer · edited May 23 '17 at 12:16

0

The For XML Path method of doing a Concatenation aggregation is one of the more popular ways to do what you're looking to do here.

See this post for details.

An application to your situation might look like this:

SELECT B.object_id, B.name, B.type_desc, 
    STUFF((SELECT A.[text] 
                        FROM sys.syscomments AS A
                        WHERE A.id = B.[object_id]
                        FOR XML PATH('')), 1, 1, '' ) AS [text]
FROM 
    sys.objects AS B
WHERE type_desc LIKE 'SQL_STORED_PROCEDURE' OR type_desc LIKE 'SQL_SCALAR_FUNCTION'
ORDER BY B.[object_id];

edited May 23 '17 at 12:16

Community

1
1

answered May 16 '16 at 19:19

Sturgus

666
4
18

Yeah that works! But is there a way to not have all the XML markups included in the result? – JulioQc May 16 '16 at 19:58
1

got it by changing FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') – JulioQc May 16 '16 at 20:22

Concatenate columns strings for matching rows

2 Answers2