Concatenation in SQL, Using For XML PATH(), Concatenate more than one rows in one field

Some times it is required to concatenate more no of rows into one single filed separated by commas. There are some options for that like we can write our own UDF for that, in sql server built in COALESCE function is there with this function we can achieve it. But some time need is different or we want to achieve it in different way then there is another option that I am going to explain using FOR XML PATH().

Let me first show me my table design

and also show me the data which I have inserted for this testing purpose

Have you tried to access data in xml format from sql table, not yet ? Then try below query and see the result.

SELECT Employee_Name FROM Emp_Tech FOR XML path('')

When you run above query then you can see result like <Employee Name>Tarun</Employee Name>.... conti... and when you click on the resulted row then it will open .xml file in query window. But here we are understanding how to merge that all the rows into single row with comma separated so let me write a query for that.

SELECT

 DISTINCT

Employee_tech,employee_name =

 substring( (SELECT

 ',' + Employee_Name

 FROM

Emp_Tech et2

 Where

et2.Employee_Tech = et1.Employee_Tech

 FOR XML PATH(''), ELEMENTS

),2,500)

FROM

Emp_Tech et1

And this is the out put for that query

Out put