Row concatenation with FOR XML, but with multiple columns?

Question

I often use queries like:

SELECT *
FROM   ThisTable
       OUTER APPLY (SELECT (SELECT SomeField + ' ' AS [data()]
                            FROM   SomeTable
                            WHERE  SomeTable.ID = ThisTable.ID
                            FOR XML PATH ('')) AS ConcatenatedSomeField) A 

I often want to get multiple concatenated concatenated fields from this table, instead of just one. I could logically do this:

SELECT *
FROM   ThisTable
       OUTER APPLY (SELECT (SELECT SomeField + ' ' AS [data()]
                            FROM   SomeTable
                            WHERE  SomeTable.ID = ThisTable.ID
                            FOR XML PATH ('')) AS ConcatenatedSomeField) A
       OUTER APPLY (SELECT (SELECT SomeField2 + ' ' AS [data()]
                            FROM   SomeTable
                            WHERE  SomeTable.ID = ThisTable.ID
                            FOR XML PATH ('')) AS ConcatenatedSomeField2) B
       OUTER APPLY (SELECT (SELECT SomeField3 + ' ' AS [data()]
                            FROM   SomeTable
                            WHERE  SomeTable.ID = ThisTable.ID
                            FOR XML PATH ('')) AS ConcatenatedSomeField3) C 

But it looks crappy and error prone when anything needs to be updated; also SomeTable is often a long list of joined tables so it could also have performance implications getting the same tables over and over.

Is there a better way to do this?

Thanks.

Answer 1

You could do something like this. Instead of immediately sending the XML value to a string, this query uses the TYPE keyword to return an xml type object which can then be queried. The three query functions search the xml object for all instances of the Somefield element and return a new xml object containing just those values. Then the value function strips out the xml tags surrounding the values and passes them into a varchar(max)

SELECT  ThisTable.ID
       ,[A].query('/Somefield').value('/', 'varchar(max)') AS [SomeField_Combined]
       ,[A].query('/Somefield2').value('/', 'varchar(max)') AS [SomeField2_Combined]
       ,[A].query('/Somefield3').value('/', 'varchar(max)') AS [SomeField3_Combined]
FROM    ThisTable
        OUTER APPLY (
                     SELECT (
                             SELECT SomeField + ' ' AS [SomeField]
                                   ,SomeField2 + ' ' AS [SomeField2]
                                   ,SomeField3 + ' ' AS [SomeField3]
                             FROM   SomeTable
                             WHERE  SomeTable.ID = ThisTable.ID
                            FOR
                             XML PATH('')
                                ,TYPE
                            ) AS [A]
                    ) [A]

Answer 2

You can create a CLR User-Defined Aggregate Function that does the concatenation for you.

Your code would then look like this instead.

select S.ID,
       dbo.Concat(S.SomeField1), 
       dbo.Concat(S.SomeField2),
       dbo.Concat(S.SomeField3)
from SomeTable as S
group by S.ID

Answer 3

This is the same answer as I gave here: https://dba.stackexchange.com/questions/125771/multiple-column-concatenation/

The OP of that question referenced the answer given here. You can see below that sometimes the simplest answer can be the best. If SomeTable is multiple tables then I would go ahead and put it into a CTE to avoid having the same complex code multiple times.

I ran a few tests using a little over 6 mil rows. With an index on the ID column.

Here is what I came up with.

Your initial query:

SELECT * FROM (
    SELECT t.id,
            stuff([M].query('/name').value('/', 'varchar(max)'),1,1,'') AS [SomeField_Combined1],
            stuff([M].query('/car').value('/', 'varchar(max)'),1,1,'') AS [SomeField_Combined2]
    FROM dbo.test t
    OUTER APPLY(SELECT (
                    SELECT id, ','+name AS name
                    ,','+car AS car
                    FROM test WHERE test.id=t.id
                    FOR XML PATH('') ,type)
                 AS  M) 
            M ) S
GROUP BY id, SomeField_Combined1, SomeField_Combined2 

This one ran for ~23 minutes.

I ran this version which is the version I first learned. In some ways it seems like it should take longer but it doesn't.

SELECT test.id,
    STUFF((SELECT ', ' + name
            FROM   test ThisTable
            WHERE  test.id = ThisTable.id
            FOR XML PATH ('')),1,2,'') AS ConcatenatedSomeField,
    STUFF((SELECT ', ' + car
            FROM   test ThisTable
            WHERE  test.id = ThisTable.id
            FOR XML PATH ('')),1,2,'') AS ConcatenatedSomeField2
FROM test 
GROUP BY id

This version ran in just over 2 minutes.