SQL Server : Comma seperated values from table column - Multiple Solutions

In some cases we need to get comma separated or any user defined separator values from table column.
There are multiple solutions for that.

Here is example :
Create one temp table and insert some records.
This table is two column one sport_name and one identity column.

    Create table #TestSport (sport_name varchar(max), id int not null identity) on [Primary]
    GO
    INSERT INTO #TestSport (sport_name) VALUES ('cricket')
    INSERT INTO #TestSport (sport_name) VALUES ('hockey')
    INSERT INTO #TestSport (sport_name) VALUES ('football')
    INSERT INTO #TestSport (sport_name) VALUES ('baseball')
   
    GO

Now if we want comma seperated value of sport_name columns.
Here are some scripts.

-- Get CSV values using SUBSTRING Function

SELECT SUBSTRING(
(SELECT ',' + s.sport_name
FROM #TestSport s
ORDER BY s.sport_name
FOR XML PATH('')),2,200000) AS CSV_USING_SUBSTRING

Output : 

-- Get CSV values Using STUFF Function

SELECT STUFF(
(SELECT ',' + s.sport_name
FROM #TestSport s
ORDER BY s.sport_name
FOR XML PATH('')),1,1,'') AS CSV_USING_STUFF

Output : 

-- Get CSV values Using COALESCE Function

DECLARE @Csv varchar(Max)
SELECT @Csv= COALESCE(@Csv + ',', '') +
CAST(s.sport_name AS varchar(50))
FROM #TestSport s
ORDER BY s.sport_name
SELECT @Csv as CSV_USING_COALESCE

Output :