Display count of rows from one table in columns in another without knowing how many columns there would be

Question

Let's say I have a Library database and the following tables in it:

• Books(ID, Title, Author, PublihserID, LanguageCode, Genre, DatePublished, ISBN) - contains examples of books as abstract items, intellectual work

ID	Title	Author	PublisherID	LanguageCode	Genre	DatePublished	ISBN
1	Death On The Nile	Agatha Christie	2	EN	Novel	1937-11-01	4215574186436

• Branches(ID, Name, City, Address) - contains the branches of the library in different cities

ID	Name	City	Address
1	Name1	London	Address1
2	Name2	Birmingham	Address2
3	Name3	Manchester	Address3

• BookCopies(ID, BookID, BranchID, Condition) - contains books as physical items and what branch they are located in

ID	BookID	BranchID	Condition
3aa99df2-7a88-4ca8-a965-046f478ac9f3	1	1	Poor
34beeffa-14c9-4796-a61c-0477be59af0f	1	1	Excellent
1dc0e7cd-0f9e-42b2-829a-04de9a77ae47	1	2	Average
88f0045c-3910-4fd6-9a29-078c2d48bfb8	1	2	Good
ea3aafe9-0ada-4396-9ed3-0867912b7958	1	2	Poor
6d003fd5-83e7-4df5-9aa8-08dd61d53eb2	1	3	Excellent
...	...	...	...

I want to write a query, which for each Book in Books displays how many physical copies of the book there are in each branch of the library. So far my data in the database has three Branches with IDs 1, 2 and 3. I have written the following query, which properly displays what I want (with some added extra such as filtering by author Agatha Christie):

SELECT
    Books.Title,
    Books.PublisherID,
    Books.DatePublished,
    Books.ISBN,
    r1.Branch1,
    r1.Branch2,
    r1.Branch3
FROM
    Books
    JOIN
    (SELECT
        copies.BookID,
        br1.Branch1,
        br2.Branch2,
        br3.Branch3
    FROM
        BookCopies copies
        JOIN
        (SELECT
            BookCopies.BookID,
            COUNT(BookCopies.BranchID) AS Branch1
        FROM
            BookCopies
        GROUP BY BookCopies.BookID, BookCopies.BranchID
        HAVING BookCopies.BranchID=1) br1
        ON copies.BookID=br1.BookID
        FULL JOIN
        (SELECT
            BookCopies.BookID,
            COUNT(BookCopies.BranchID) AS Branch2
        FROM
            BookCopies
        GROUP BY BookCopies.BookID, BookCopies.BranchID
        HAVING BookCopies.BranchID=2) br2
        ON copies.BookID=br2.BookID
        FULL JOIN
        (SELECT
            BookCopies.BookID,
            COUNT(BookCopies.BranchID) AS Branch3
        FROM
            BookCopies
        GROUP BY BookCopies.BookID, BookCopies.BranchID
        HAVING BookCopies.BranchID=3) br3
        ON copies.BookID=br3.BookID
    GROUP BY copies.BookID, br1.Branch1, br2.Branch2, br3.Branch3) r1
    ON Books.ID=r1.BookID
WHERE Books.Author='Agatha Christie'

Title	Publisher	DatePublished	ISBN	Branch1	Branch2	Branch3
Death on the Nile	2	1937-11-01	4215574186436	2	3	1
...	...	...	...	...	...	...

But there are two fundamental problems with such a query:

1. I am sure it's really slow, ineffective and full of bad practices
2. It is hardcoded for three branches. It wouldn't work if the library opened a fourth, fifth etc branch.
   
   How can I improve my query so that it works for all branches (no matter their count) and preferrably uses resources efficiently?

Answer 1

Basically, you don't. Formatting is a job for the presentation layer, in the database layer you focus on getting the correct results:

SELECT BookID, BranchID, COUNT(*)
FROM BookCopies
GROUP BY BookID, BranchID

Gives you the counts for each book/branch combination. Join with Books and Branches:

SELECT b.Title
    ,  b.PublisherID
    ,  b.DatePublished
    ,  b.ISBN
    ,  r.Branch
    ,  x.CNT
FROM Books b
JOIN (
    SELECT BookID, BranchID, COUNT(*) AS CNT
    FROM BookCopies
    GROUP BY BookID, BranchID
) AS x
    USING (BookID)
JOIN Branches r
    USING (BranchID)
ORDER BY b.BookID, r.BranchID

In your application, you can loop over that resultset. When bookid changes you make a new row in your "report". for every new branchid you make a new column

EDIT: SQL server does not support USING clause:

Fiddle

SELECT b.ID as BookID
    ,  b.Title
    ,  b.PublisherID
    ,  b.DatePublished
    ,  b.ISBN
    ,  r.ID as BranchID
    ,  r.Name as Branch
    ,  x.CNT
FROM Books AS b
JOIN (
    SELECT BookID, BranchID, COUNT(*) AS CNT
    FROM BookCopies
    GROUP BY BookID, BranchID
) AS x
    ON b.ID = x.BookID
JOIN Branches AS r
    ON r.ID = x.BranchID
ORDER BY b.ID, r.ID;

According to the comment, this is a course project looking for ideas. Some things that might be worth investigating are:

STRING_AGG

Window functions

GROUP BY ROLLUP, CUBE, GROUPING SETS

CTE, Common Table Expressions

Temporal tables