Query showing list of associations in many-to-many relationship

Question

Please note that the same question already has been asked in http://stackoverflow.com/questions/111341/combine-multiple-results-in-a-subquery-into-a-single-comma-separated-value, but the solution involves creating a function. I am asking if there is a way to solve this without having to create a function or a stored procedure.

---

I have two tables, Book and Tag, and books are tagged using the association table BookTag. I want to create a report that contains a list of books, and for each book a list of the book's tags. Tag IDs will suffice, tag names are not necessary.

Example:

Book table:
Book ID | Book Name
28      | Dracula

BookTag table:
Book ID | Tag ID
28      | 101
28      | 102

In my report, I'd like to show that book #28 has the tags 101 and 102:

Book ID | Book Name | Tags
28      | Dracula   | 101, 102

Is there a way to do this in-line, without having to resort to functions or stored procedures? I am using SQL Server 2005.

Answer 1

Unless you know what the tag ids/names are and can hard code them into your query, I'm afraid the answer is no.

Answer 2

If you knew the maximum number of tags for a book, you could use a pivot to get them to the same row and then use COALESCE, but in general, I don't believe there is.

Answer 3

You can almost do it. The only problem I haven't resolved is the comma delimiter. Here is a query on a similar structure that separates the tags using a space.

SELECT em.Code,
    (SELECT et.Name + ' '  AS 'data()'
     FROM tblEmployeeTag et
      JOIN tblEmployeeTagAssignment eta ON et.Id = eta.EmployeeTag_Id AND eta.Employee_Id = em.id
    FOR XML PATH('') ) AS Tags
FROM tblEmployee em

Edit:

Here is the complete version using your tables and using a comma delimiter:

SELECT bk.Id AS BookId,
     bk.Name AS BookName,
    REPLACE((SELECT LTRIM(STR(bt.TagId)) + ', '  AS 'data()'
     FROM BookTag bt
     WHERE bt.BookId = bk.Id   
     FOR XML PATH('') ) + 'x', ', x','') AS Tags
FROM Book bk

I suppose for future reference I should explain a bit about what is going on. The 'data()' column name is a special value that is related to the FOR XML PATH statement. It causes the XML document to be rendered as if you did an .InnerText on the root node of the resulting XML.
The REPLACE statement is a trick to remove the trailing comma. By appending a unique character (I randomly chose 'x') to the end of the tag list I can search for comma-space-character and replace it with an empty string. That allows me to chop off just the last comma. This assumes that you are never going to have that sequence of characters in your tags.

Answer 4

The cleanest solution is probably to use a custom C# CLR aggregate function. We have found that this works really well. You can find instructions for creating this at http://dotnet-enthusiast.blogspot.com/2007/05/user-defined-aggregate-function-in-sql.html