How To Return 1 Single Row Data From 2 Different Tables With Dynamic Contents In Sql

Can someone provide answer to this situation?? Suppose I have 2 tables: Table Books with values Batch_no and Title Batch_no - Title 1 - A 2 - B and; Table Book_Authors with valu

Solution 1:

If you take a look here: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

there are several techniques you can do this.

Adapting for your situation, here is one that looks simple:

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

          ORDERBY Author_no

            FOR XML PATH('') ) AS BooksAuthorsFROM Book_Authors ba

      GROUPBY Batch_no )A;