Answered by:
How to put column data into rows

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