Consolidating rows using FOR XML, but with multiple columns?

Question

I often use queries such as:

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 several concatenated concatenated fields from this table, and not 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 he looks crap and error prone when something needs to be updated; also SomeTable is often a long list of joined tables, so it can also have performance implications by getting the same tables over and over again.

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 object of type xml, which can then be requested. The three query functions look for an xml object for all instances of the Somefield element and return a new xml object containing only these values. The value function then preempts the xml tags surrounding the values ​​and passes them to 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 concatenation for you.

Then your code will look like this.

 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 am here: https://dba.stackexchange.com/questions/125771/multiple-column-concatenation/

The OP of this question refers to the answer given here. You can see below that sometimes the simplest answer may be the best. If SomeTable are multiple tables, I would go ahead and put it in the CTE to avoid the same complex code several times.

I did some tests using a little over 6 million lines. With an index in the ID column.

Here is what I came up with.

Your initial request:

 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 went on for about 23 minutes.

I launched this version, the version of which I first found out. In a way, it seems like it will take longer, but it is not.

 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 earned a little over 2 minutes.