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

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

Leave a Reply