locked
How to pivot horizontally Author names, and group by Book title. One row per book with multiple authors RRS feed

  • Question

  • I have 3 tables - Book, Author, BookAuthorReference
    A book can have multiple authors, and when I do straight query I get multiple rows per book

    SELECT <columns>
    FROM Book b, Author a, BookAuthorReference ba
    where ba.BookId = b.BookId and
    ba.AuthorId = a.AuthorId

    I want to get the results as ONE row per book, and Authors separated by commas, like:

    SQL 2008 internals book    Paul Randal, Kimberly Tripp, Jonathan K, Joe Sack...something like this

    Thank you in advance

    Saturday, January 10, 2015 8:02 PM

Answers

  • This can by done by straying into XML land. The syntax is anything but intuitive, but it works. And moreover, it is guaranteed to work.

    SELECT b.Title, substring(a.Authors, 1, len(a.Authors) - 1) AS Authors
    FROM   Books b
    CROSS  APPLY (SELECT a.Author + ','
                  FROM   BookAuthorReference ba
                  JOIN   Authors a ON a.AuthorID = ba.AuthorID
                  WHERE  ba.BookID = a.BookID
                  ORDER  BY ba.AuthorNo
                  FOR XML PATH('')) AS a(Authors)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, January 10, 2015 8:30 PM

All replies

  • This can by done by straying into XML land. The syntax is anything but intuitive, but it works. And moreover, it is guaranteed to work.

    SELECT b.Title, substring(a.Authors, 1, len(a.Authors) - 1) AS Authors
    FROM   Books b
    CROSS  APPLY (SELECT a.Author + ','
                  FROM   BookAuthorReference ba
                  JOIN   Authors a ON a.AuthorID = ba.AuthorID
                  WHERE  ba.BookID = a.BookID
                  ORDER  BY ba.AuthorNo
                  FOR XML PATH('')) AS a(Authors)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, January 10, 2015 8:30 PM
  • Thank you! I played around with it a bit and it works as advertized :)
    Sunday, January 11, 2015 5:12 AM