Get Comma seperated AuthorName from AuthorMaster table in select query of Books table in MSSQL

Question

I have two tables: 1. AuthorMaster and 2. Books

AuthorMaster
----------------
ID     Name
1      ABC
2      XYZ
3      PQR

Books
----------------
ID   BookName       AuthorIds
1    MATHEMATICS    2,3
2    Briar Queen    1,3


I need output like
ID    BookName      AuthorNames
1     MATHEMATICS   XYZ, PQR
2     Briar Queen   ABC, PQR

I've tried to create a separate stored procedure to get Author Names:

DECLARE @Query VARCHAR(MAX)
    declare @t table(AuthorNames varchar(max))

    SET @Query = 'DECLARE @AuthorNames VARCHAR(MAX); SELECT @AuthorNames = COALESCE( @AuthorNames + '', '', '''') + AuthorName FROM AuthorMasters WHERE AuthorId IN (' + @IDs + ') AND ISNULL(IsDelete, 0) = 0; SELECT @AuthorNames'

    INSERT INTO @t
    Execute(@Query)

    SELECT @AuthorNames = AuthorNames FROM @t

But I can't execute it inside a select statment.

SELECT ID, BookName, <how to call sp where i can pass Books.AuthorIds?> FROM Books

@ZoharPeled my question is different because I need to fetch value from another table, not from the same table. Please check and un-mark my question, I have try many solution but I am not getting my result. — Jigarb1992, Apr 02 '19 at 09:21
You can use a correlated subquery to get the comma delimited values from the other table - not much difference — Zohar Peled, Apr 02 '19 at 09:22
BTW, Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** — Zohar Peled, Apr 02 '19 at 09:22
I have try to pass subquery but it throw error 'Conversion failed when converting the varchar value '1,2' to data type int.' — Jigarb1992, Apr 02 '19 at 09:23
because you are storing multiple values in a single column. The correct way to create a many to many relationship in a relational database is using a bridge table. — Zohar Peled, Apr 02 '19 at 09:24
I know it is bad to store delimited list in column, I have not created this database and in this stage when my project is about to complete, I cant change the database structure — Jigarb1992, Apr 02 '19 at 09:26
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/191075/discussion-between-jigarb1992-and-zohar-peled). — Jigarb1992, Apr 02 '19 at 09:26
On second thought, You're correct. This question is a bit more complicated than a simple string aggregation. I've reopened it and provided a complete answer. — Zohar Peled, Apr 02 '19 at 09:55

score 1 · Accepted Answer · answered Apr 02 '19 at 09:54

Prior to SQL Server 2017, when string_agg was finally introduced as a built in function, the common method of concatenating strings from different rows was to use a combination of stuff and for xml path('') as demonstrated here.

Now, let's create and populate the sample tables (Please save us this step in your future questions):

CREATE TABLE AuthorMaster
(
    id int, 
    Name char(3)
);

INSERT INTO AuthorMaster (ID, Name) VALUES
(1, 'ABC'),
(2, 'XYZ'),
(3, 'PQR');

CREATE TABLE Books
(
    ID int,
    BookName varchar(100),
    AuthorIds varchar(100)
);

INSERT INTO Books (ID, BookName, AuthorIds) VALUES
(1, 'MATHEMATICS', '2,3'),
(2, 'Briar Queen', '1,3');

The problem with this design is the fact that instead of creating a BookToAuthor bridge table to create the many-to-many relationship, The database designer used a terrible idea and decided to store the author ids as a delimited string in the database. (Why is this such a terrible idea?)

To overcome this, we need to split this delimited string. Alas, prior to SQL Server 2016, when string_split was finally introduced, you had to use a user defined function to split a delimited string.
Aaron Bertrand have published an article called Split strings the right way – or the next best way back in 2012 where he compares different string splitting functions. I've used one of his examples from that article on this answer - the SplitStrings_XML function:

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

So now that we have the sample data and the string splitting function, let's go to the query. Using the string aggregation query on the names of the authors, where the ids are in the AuthorIds column, and cross apply to bind that into the books table, Here is the query I came up with:

SELECT  ID, 
        BookName,
        AuthorsNames
FROM Books
CROSS APPLY
(
    SELECT STUFF(
        (
        SELECT ','+ Name
        FROM AuthorMaster
        WHERE Id IN
        (
            SELECT Item 
            FROM dbo.SplitStrings_XML(AuthorIds, ',')
        )
        FOR XML PATH('')
        ), 1, 1, '') As AuthorsNames
) As Names

And here are it's results:

ID  BookName        AuthorsNames
1   MATHEMATICS     XYZ,PQR
2   Briar Queen     ABC,PQR

Thanks for your help @ZoharPeled, Solution fit to my problem. — Jigarb1992, Apr 02 '19 at 10:17

Get Comma seperated AuthorName from AuthorMaster table in select query of Books table in MSSQL

1 Answers1