Apr 8, 2011

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






4 comments:

bdface.tk

wel.....

Anonymous

Thanks, this helped out a lot!

Anonymous

Great! Is there a way to get the resulting datatype to be nvarchar(255) rather than nvarchar(max)?

Daniel Comp

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

Author Profile

Total Pageviews

Categories

Followers

 

Sponsors