How to combine several rows in one row, separated by commas

The best way to combine values from multiple rows in one is to create user-defined function that does the job, and then just call the function from the SELECT statement.

For example lets take pubs data author and title tables. Let's say we want to retrieve ALL the titles for each author, separated by comma.

Here is a sample ud function that utilizes COALESCE for concatenation of all the titles for a every author:

CREATE FUNCTION dbo.AuthorTitles ( @author varchar(100) )
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @RES VARCHAR(8000)
  SELECT @RES = COALESCE(@RES + ',','') + title
  FROM titles as t
INNER JOIN titleauthor as ta ON t.title_id=ta.title_id
INNER JOIN authors as a on ta.au_id=a.au_id
  WHERE a.au_lname = @author
  RETURN (@RES)
END

and this SELECT statement will display the results, grouped by author's last name:

SELECT DISTINCT a.au_lname,dbo.AuthorTitles(a.au_lname)
FROM authors as a 
INNER JOIN titleauthor as ta ON a.au_id=ta.au_id
INNER jOIN titles as t ON ta.title_id=t.title_id
GROUP BY a.au_lname,t.title