Multiple rows to one comma-separated value in sql

Question

We want to display multiple column value into the single column with comma separated value below is the my table data

Below is the SQL query i am trying:

 select name, STUFF((SELECT '; ' + facilty FROM leads Name FOR XML PATH('')),1,2,'') as facilty, address from leads

Query Result

Now when I execute the query in SQL Server, I want reuslt to be like:

Gordon Linoff · Accepted Answer · 2020-05-28 13:56:51Z

2

Use string_agg():

select max(name), string_agg(facility, ','), max(address) from leads group by leadid;

answered May 28, 2020 at 13:56

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Would be helpful to mention that STRING_AGG is supported only if you are using SQL Server version 2017 and higher

@PriyankPanchal . . . It was released three years ago and isn't even the current version.

Yogesh Sharma · Accepted Answer · 2020-05-28 14:05:08Z

0

Use where clause :

select name , STUFF((SELECT '; ' + facilty FROM leads FOR XML PATH('') ),1,2,'') as facilty, address from leads where name is not null;

answered May 28, 2020 at 13:57

50.2k5 gold badges31 silver badges53 bronze badges

this working fine i know. is not null condition is working but i want some other method to do this