locked
How to put column data into rows RRS feed

  • Question

  • Hi all,

    How do I display, book id and book name for an author on the same row?

     Want up to 4 books for the same author to display on same row

                                                                                                                           

    --https://riptutorial.com/sql/example/4978/library-database

    -- create and load Authors table

    CREATE TABLE Authors (

        Id INT NOT NULL IDENTITY(1, 1),

        Name VARCHAR(70) NOT NULL,

        Country VARCHAR(100) NOT NULL,

        PRIMARY KEY(Id)

    );

    INSERT INTO Authors

        (Name, Country)

    VALUES

        ('J.D. Salinger', 'USA'),

        ('F. Scott. Fitzgerald', 'USA'),

        ('Jane Austen', 'UK'),

        ('Scott Hanselman', 'USA'),

        ('Jason N. Gaylord', 'USA'),

        ('Pranav Rastogi', 'India'),

        ('Todd Miranda', 'USA'),

        ('Christian Wenz', 'USA')

    ;

    -- create and load Books table

    CREATE TABLE Books (

        Id INT NOT NULL IDENTITY(1, 1),

        Title VARCHAR(50) NOT NULL,

        PRIMARY KEY(Id)

    );

    --INSERT INTO Books

    --    (Id, Title)

    --VALUES

    --    (1, 'The Catcher in the Rye'),

    --    (2, 'Nine Stories'),

    --    (3, 'Franny and Zooey'),

    --    (4, 'The Great Gatsby'),

    --    (5, 'Tender id the Night'),

    --    (6, 'Pride and Prejudice'),

    --    (7, 'Professional ASP.NET 4.5 in C# and VB')

    --;

    INSERT INTO Books

        (Title)

    VALUES

        ('The Catcher in the Rye'),

        ('Nine Stories'),

        ('Franny and Zooey'),

        ('The Great Gatsby'),

        ('Tender id the Night'),

        ('Pride and Prejudice'),

        ('Professional ASP.NET 4.5 in C# and VB')

    ;

    -- create and load BooksAuthors

    CREATE TABLE BooksAuthors (

        AuthorId INT NOT NULL,

        BookId  INT NOT NULL,

        FOREIGN KEY (AuthorId) REFERENCES Authors(Id),

        FOREIGN KEY (BookId) REFERENCES Books(Id)

    );

    INSERT INTO BooksAuthors

        (BookId, AuthorId)

    VALUES

        (1, 1),

        (2, 1),

        (3, 1),

        (4, 2),

        (5, 2),

        (6, 3),

        (7, 4),

        (7, 5),

        (7, 6),

        (7, 7),

        (7, 8)

    ;

    -- display data in the Authors table

    SELECT * FROM Authors;

    -- display data in the Books table

    SELECT * FROM Books;

    -- view books and their authors

    SELECT

      ba.AuthorId,

      a.Name AuthorName,

      ba.BookId,

      b.Title BookTitle

    FROM BooksAuthors ba

      INNER JOIN Authors a ON a.id = ba.authorid

      INNER JOIN Books b ON b.id = ba.bookid

    ;

    Thanks,

    Seyed

    • Moved by Dave PatrickMVP Monday, December 28, 2020 4:56 PM looking for forum
    Monday, December 28, 2020 4:52 PM

Answers

All replies

  • I'd try asking for help over here.

    office-excel-itpro - Microsoft Q&A

    sql-server-general - Microsoft Q&A

     

     



    Regards, Dave Patrick ....
    Microsoft Certified Professional
    Microsoft MVP [Windows Server] Datacenter Management

    Disclaimer: This posting is provided "AS IS" with no warranties or guarantees, and confers no rights.

    • Proposed as answer by Dave PatrickMVP Wednesday, December 30, 2020 10:06 PM
    • Marked as answer by Guido Franzke Monday, January 4, 2021 8:27 AM
    Monday, December 28, 2020 4:56 PM
  • Hi Dave,

    Do I need to re-post this question in sql-server-general, or you moved this question there?

    Thanks,

    Monday, December 28, 2020 5:02 PM
  • You'll need to re-ask your question over there.

     

     



    Regards, Dave Patrick ....
    Microsoft Certified Professional
    Microsoft MVP [Windows Server] Datacenter Management

    Disclaimer: This posting is provided "AS IS" with no warranties or guarantees, and confers no rights.

    Monday, December 28, 2020 5:03 PM