Fetching Comma Separated Value In One Column MSSQL

In this example i am going to describe how to combine multiple records in a column in MS SQL into one record comma separated.I have used the customer table of the northwind database. What I want to do is to get the Country name in one column and then in the second column I want to have company names separated by the comma. Here is the output of the simple select statement.

And here is the out put of the desired result.

Here is the script used to produce the comma separated values. Here I have first declared the local variable which is used to save the comma separated values and I have set the length of the variable to 1000, you can change it to your desired capacity. In the next statement I have used the @EmployeeNames variable to store the Comma separated value. And I have used the Coalesce function which is used to returns the first nonnull expression among its arguments.

Declare @EmployeeNames VARCHAR(1000)
Select  @EmployeeNames=Coalesce(@EmployeeNames,'') + CompanyName +';' from Customers where country='UK'
Select  distinct Country,@EmployeeNames As CompanyName from Customers where Country='UK'

And at the end I have used the simple select statement to fetch the record with the same where clause which is used in assigning the @EmployeeNames variable.

All and any comments / bugs / suggestions are welcomed!