The following code snippet will create a comma separated list of values, based on selection parameters for a given field. Using SQL COALESCE, you can easily adapt the following code to fit your needs.
By Definition: COALESCE Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL. You can then specify which row number you would like to see in your result set.
The actual SQL code is quite simple:
DECLARE @String VARCHAR(MAX)
@String = COALESCE(@String+',' ,'') + p.Player
FROM PlayerStats p
WHERE p.NFLTeam = 'BUF'
and p.NFLYear = 2014
and p.NFLWeek = 1
ORDER BY p.NFLWeek
SELECT @String as CommaList
For simplicity, say you have the following data, which contains a list of players on a specific team, during week 1 of the 2014 NFL regular season (who recorded at least one fantasy football stat):
Now, let’s say you’d like to create a comma separated list of these players. By running the code snippet above, you will get the following results (I limited the display to the first three players on the list, for display purposes):
The SQL COALESCE is handy when you are trying to generate a comma separated list of values for a specific field. You can modify this code to meet your needs.