How to move column data into rows

Question

Hi all,
How do I display, all book ids and book names of an author on the same row?

I would like to display up to 4 books for the same author to display on same row.

How is this done?

--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  
;  
  

Thank you,

Seyed

Answer 1

If you like the PIVOT statement, then check a solution:

;
with E1 as
(
    select *, row_number() over( partition by AuthorId order by BookId) as bn
    from BooksAuthors ba
),
E2 as
(
    select AuthorId, [1], [2], [3]
    from E1
    pivot
    (
        max(BookId) for bn in ([1], [2], [3])
    ) p
)
select a.Id as [Author ID], a.Name as Author, 
    e.[1] as BookID_1,
    b1.Title as Book_1,
    e.[2] as BookID_1,
    b2.Title as Book_2,
    e.[3] as BookID_3,
    b3.Title as Book_3
from E2 e
inner join Authors a on a.Id = e.AuthorId
inner join Books b1 on b1.Id = e.[1]
left join Books b2 on b2.Id = e.[2]
left join Books b3 on b3.Id = e.[3]

Can be extended for 4 and more books.

Answer 2

Try this:

DECLARE @Author TABLE (
    AuthorID int,
    Author varchar(50),
    BookID int,
    Book varchar(100)
);
INSERT INTO @Author VALUES
(1, 'A1', 1, 'B1'),(1, 'A1', 2, 'B2'),(1, 'A1', 3, 'B3'),(2, 'A2', 4, 'B4'),
(2, 'A2', 5, 'B5'),(3, 'A3', 6, 'B6'),(4, 'A4', 7, 'B7'),(5, 'A5', 7, 'B7'),
(6, 'A6', 7, 'B7'),(7, 'A7', 7, 'B7'),(8, 'A8', 7, 'B7');

;WITH CTE_Author_Book_Order AS (
    SELECT AuthorID, Author, BookID, Book, ROW_NUMBER() OVER(PARTITION BY AuthorID ORDER BY BookID) AS RN
    FROM @Author
),
CTE_Author_Book_1 AS (
    SELECT AuthorID, Author, BookID, Book 
    FROM CTE_Author_Book_Order
    WHERE RN = 1
),
CTE_Author_Book_2 AS (
    SELECT AuthorID, Author, BookID, Book 
    FROM CTE_Author_Book_Order
    WHERE RN = 2
),
CTE_Author_Book_3 AS (
    SELECT AuthorID, Author, BookID, Book 
    FROM CTE_Author_Book_Order
    WHERE RN = 3
),
CTE_Author_Book_4 AS (
    SELECT AuthorID, Author, BookID, Book 
    FROM CTE_Author_Book_Order
    WHERE RN = 4
)

SELECT c1.AuthorID, c1.Author, c1.BookID AS BookID_1, c1.Book AS Book_1, 
       ISNULL(CAST(c2.BookID AS varchar(10)), '') AS BookID_2, ISNULL(c2.Book, '') AS Book_2, 
       ISNULL(CAST(c3.BookID AS varchar(10)), '') AS BookID_3, ISNULL(c3.Book, '') AS Book_3, 
       ISNULL(CAST(c4.BookID AS varchar(10)), '') AS BookID_4, ISNULL(c4.Book, '') AS Book_4
FROM CTE_Author_Book_1 AS c1
LEFT JOIN CTE_Author_Book_2 AS c2 ON c1.AuthorID = c2.AuthorID
LEFT JOIN CTE_Author_Book_3 AS c3 ON c1.AuthorID = c3.AuthorID
LEFT JOIN CTE_Author_Book_4 AS c4 ON c1.AuthorID = c4.AuthorID;

Answer 3

Hi

SELECT        dbo.Authors.Id, dbo.Authors.Name, dbo.Authors.Country, dbo.BooksAuthors.BookId, dbo.Books.Title
FROM            dbo.Authors INNER JOIN
                         dbo.BooksAuthors ON dbo.Authors.Id = dbo.BooksAuthors.AuthorId INNER JOIN
                         dbo.Books ON dbo.BooksAuthors.BookId = dbo.Books.Id

1   J.D. Salinger   USA 1   The Catcher in the Rye
1   J.D. Salinger   USA 2   Nine Stories
1   J.D. Salinger   USA 3   Franny and Zooey
2   F. Scott. Fitzgerald    USA 4   The Great Gatsby
2   F. Scott. Fitzgerald    USA 5   Tender id the Night
3   Jane Austen UK  6   Pride and Prejudice
4   Scott Hanselman USA 7   Professional ASP.NET 4.5 in C# and VB
5   Jason N. Gaylord    USA 7   Professional ASP.NET 4.5 in C# and VB
6   Pranav Rastogi  India   7   Professional ASP.NET 4.5 in C# and VB
7   Todd Miranda    USA 7   Professional ASP.NET 4.5 in C# and VB
8   Christian Wenz  USA 7   Professional ASP.NET 4.5 in C# and VB

Best Regards.

Please click the Mark as answer button and vote as helpful if this reply solves your problem.