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

All we need is an easy explanation of the problem, so here it is.

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

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

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

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'

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0