Combine Authors with similar IDs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combine Authors with similar IDs

Author

Topic

Lebowski
Starting Member

17 Posts

Posted - 2014-03-13 : 01:17:13

Im trying this simple query, where if a book has the same ID but different names it should be shown in one row and not two...

Now it is showing

- 1 AuthorDude
- 1 AuthorGirl
- 2 Authorblabla

While it should show

- 1 AuthorDude, AuthorGirl
- 2 Authorblabla

     SELECT  a.book_id, b.Name
        from BOOK_SALES a
        left outer join 
        (
         select Book_id,is_primary, 
           STUFF((select  ', ' + Name from BOOK_CONTRIBUTOR B where B.BOOK_ID = A.BOOK_ID
                               for xml path('')),1,1,'') Name
        from BOOK_CONTRIBUTOR A
        group by Book_id,IS_PRIMARY 
        ) b  on a.BOOK_ID = b.BOOK_ID

When i do a inner join, it would just show many Book_ID`s, but with the authors combined.

http://mnmt.no

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-03-13 : 01:35:43

SELECT a.book_id,
STUFF((select ', ' + Name from BOOK_CONTRIBUTOR B where B.BOOK_ID = A.BOOK_ID
group by b.Book_id,b.IS_PRIMARY for xml path('')),1,1,'') Name
FROM BOOK_SALES AS a

Veera

Lebowski
Starting Member

17 Posts

Posted - 2014-03-13 : 01:52:32

Didnt work:

Column 'book_contributor.name' is invalid in the select list because it is not contained in either an aggregate function or the group by clause.

http://mnmt.no

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-03-13 : 02:29:21

can u plz provide the sample date in tables

Veera

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources