Geek asks and answers

How to return 1 single-row information from two different tables with dynamic content in sql

Can someone give an answer to this situation? Suppose I have 2 tables:

Book Table with Batch_no and Title

Batch_no - Header
1 - A
2 - B

and;

Book_Authors table with Batch_no and Author_no

Batch_no - Author_no
 1 - 1
 1 - 2
 1 - 3
 2 - 1

How to combine values ​​into 1 line, which should look like this:

Batch_no Author
 1 - 1, 2, 3
 2 - 1

Any help would be greatly appreciated ... Thanks so much!

0
source share
1 answer

: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

, .

- :

    select batch_no, LEFT(booksauthors, len(booksauthors)-1) as Authors from 
(SELECT ba.Batch_no,

      ( SELECT cast(ba1.Author_no as varchar(10)) + ','

           FROM Book_Authors ba1

          WHERE ba1.Batch_no = ba.Batch_no

          ORDER BY Author_no

            FOR XML PATH('') ) AS BooksAuthors

      FROM Book_Authors ba

      GROUP BY Batch_no )A;
0

More articles:


All Articles