Geek asks and answers

Merging column values ​​in many relationship relationships

I have two tables: Books and Authors , with many-to-many relationships between them through a third table called book_authors , I am trying to list all the books with authors for each book, using an internal join to display them in the DataList Control, but join causes multiple repeating lines because each book can have many authors, so there will be a line for each author.
Example:

book_title           author  
b1                    a1  
b1                    a2

What is the best way to fix this so that it becomes:

book_title                author  
b1                        a1, a2
+3
source share
3 answers

Maybe something like this:

SELECT
    Books.book_title,
    STUFF
    (
        (
            SELECT 
                ',' +author
            FROM
                book_authors
                JOIN Authors
                    ON book_authors.authorId=Authors.authorId
            WHERE
                book_authors.bookId=Books.bookid
            FOR XML PATH('')
        )
    ,1,1,'')
FROM
    Books

EDIT

, . :

DECLARE @Table1 TABLE(ID INT)
DECLARE @Table2 TABLE(Name varchar(100),ID INT)

INSERT INTO @Table1 VALUES(1),(2)
INSERT INTO @Table2 VALUES('test1',1),('test2',1),('test3',2),('test4',2)

SELECT
    t1.ID,
    STUFF
    (
        (
            SELECT 
                ',' +t2.Name
            FROM
                @Table2 AS t2
            WHERE
                t1.ID=t2.ID
            FOR XML PATH('')
        )
    ,1,1,'')
FROM
    @Table1 AS t1
+4

SQL, , yilds (csv, , ). , book_title .

SQL, , DataList, , (book_title → authorList), .

, , , sql - .

+1

What you want to do is concatenate the string pipeline. There are very good posts on this subject.

Here is an alternative that can easily work in your case, since there are not many authors in the books:

select b.name,
       (max(case when authornum = 1 then author else '' end) +
        max(case when authornum = 2 then ', '+ author else '' end) +
        max(case when authornum = 3 then ', '+ author else '' end)
       ) as authors
 from (select ba.*,
              row_number() over (partition by bookid order by authorid) as authornum
       from BookAuthors ba
      ) bajoin
      Authors a
      on a.authorid = ba.authorid join
      Books b
      on b.bookid = ba.bookid
 group by b.name

You just need to be sure that you have included enough authors in the select statement.

+1
source

More articles:


All Articles