Wednesday, July 27, 2011

Generating comma separated lists with SELECT statement

I needed to retrieve a two column data set from a database where the first column would contain the last name of the author and second column - a comma separated list of all the book titles for that particular author.

It was rather simple to generate a list of author ids and list of title ids usign SUBSTRING function and FOR XML

SELECT a.au_id,
SUBSTRING( ( SELECT ( ',' + title_id)
FROM titleauthor ta
WHERE a.au_id = ta.au_id
ORDER BY a.au_id, ta.au_id FOR XML PATH('') ), 3, 1000)
FROM authors a
GROUP BY a.au_id

To generate a list of actual full names and titles, all i had to do is add a join to titles table:

SELECT a.au_lname + ', ' + a.au_fname, SUBSTRING( ( SELECT ( ',' + '''' + t.title + '''')
FROM titles t
INNER JOIN titleauthor ta ON t.title_id=ta.title_id
WHERE a.au_id = ta.au_id
ORDER BY a.au_lname + ', ' + a.au_fname FOR XML PATH('') ), 2, 1000)
FROM authors a
GROUP BY a.au_id,a.au_lname + ', ' + a.au_fname