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
4 comments:
wel.....
Thanks, this helped out a lot!
Great! Is there a way to get the resulting datatype to be nvarchar(255) rather than nvarchar(max)?
It's funny that I struggled to understand the English grammar - and found the SQL perfectly useful and clear, whereas a dozen other sites have good explanations with the SQL NOT working (for me).
Thank you for the solution to my challenge
Post a Comment