comma-separated string | SQL Server

`STRING_AGG()`

If you have data in a SQL table that you want to concatenate and present as a comma-separated list, you can use the `STRING_AGG()` function in SQL. This function is available in SQL Server and some other database systems. It allows you to concatenate values from multiple rows into a single string with a specified separator, such as a comma.

Here's how you can use the `STRING_AGG()` function to comma-separate data in SQL:

Assuming you have a table called `MyTable` with a column named `MyColumn`, and you want to concatenate the values in `MyColumn` with commas:

SELECT STRING_AGG(MyColumn, ', ') AS CommaSeparatedData

FROM MyTable;

In this example:

- `MyColumn` is the column whose values you want to concatenate.

- `', '` is the separator you want to use. You can change it to any character or string you prefer.

This query will return a single string that contains the values from `MyColumn` separated by commas.

`STUFF` , `COALESCE()` and `FOR XML PATH` 

You can use the `COALESCE()` function in SQL to concatenate values from multiple rows into a comma-separated list.

The `COALESCE()` function returns the first non-null expression from a list of expressions.

You can use it in combination with the `FOR XML PATH` technique to achieve the desired result.

Here's how you can use `COALESCE()` to comma-separate data in SQL:

Assuming you have a table called `MyTable` with a column named `MyColumn`, and you want to concatenate the values in `MyColumn` with commas:

SELECT

    COALESCE(STUFF((

        SELECT ', ' + MyColumn

        FROM MyTable

        FOR XML PATH(''), TYPE

    ).value('.', 'VARCHAR(MAX)'), 1, 2, ''), '') AS CommaSeparatedData;

In this query:

- The subquery `FOR XML PATH(''), TYPE` is used to concatenate the values from `MyColumn` with commas. It generates an XML result with the values.

- `STUFF()` is used to remove the leading comma and space from the concatenated result.

- `COALESCE()` ensures that if there are no rows in `MyTable`, an empty string is returned instead of `NULL`.

This query will return a single string that contains the values from `MyColumn` separated by commas.