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
