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.
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.
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!