Tuesday, December 8, 2009

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) )
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

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

No comments:

Post a Comment