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
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: