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.
',' + Employee_Name
et2.Employee_Tech = et1.Employee_Tech
FOR XML PATH(''), ELEMENTS
And this is the out put for that query