Combine Rows with FOR XML PATH

We can use FOR XML PATH to combine rows.

 For example, have the following data:

And I want to combine the value of column SRC_ID_SEED from all rows to a single row as:

We can achieve this by using FOR XML PATH():

select

stuff((select ‘,’+cast(src_id_seed as varchar(20))

         from DATA_SOURCES

Choosing a Global Software Development Partner to Accelerate Your Digital Strategy

To be successful and outpace the competition, you need a software development partner that excels in exactly the type of digital projects you are now faced with accelerating, and in the most cost effective and optimized way possible.

Get the Guide

         FOR XML PATH ( ” )),

         – Function stuff used to remove the first character (,)

         1,1,”)as Code

But if you have special characters in value then you need to use Type directive to create the XML data type.

 For example: if you want the result like following

You need to use the sql with type:

 select (select ‘<‘+cast(src_id_seed as varchar(20))+’>’

from DATA_SOURCES

FOR XML PATH ( ” ),TYPE).value(‘.’,’varchar(max)’)as code

If not use Type then the result will be: