Pivot from 3 Tables with Dynamic Value

Question

I want to create a pivot table using the following table but before that here is my table

tbl_branch

tbl_product

tbl_orders

Now. all of them are dynamic, means soon I will add new branch,new item and the orders will continue. and now here is the output I want to achieve.

How can I add the total qty per branch and Item? Basically it is a pivot table. It would be easy if it is static but the 3 tables will continuous to add data.

Answer 1

An example pivot-table query is like this:

select p.description as item,
  sum(case o.branch_id when 1 then o.qty else 0 end) as `Branch 1`,
  sum(case o.branch_id when 2 then o.qty else 0 end) as `Branch 2`,
  sum(case o.branch_id when 3 then o.qty else 0 end) as `Branch 3`,
  sum(case o.branch_id when 4 then o.qty else 0 end) as `Branch 4`,
  sum(case o.branch_id when 5 then o.qty else 0 end) as `Branch 5`,
  sum(case o.branch_id when 6 then o.qty else 0 end) as `Branch 6`,
  sum(o.qty) as `Total Qty`
from tbl_product as p
join tbl_orders as o on p.id = o.item_id
join tbl_branch as b on o.branch_id = b.id
group by p.id;

Output:

+-----------+----------+----------+----------+----------+----------+----------+-----------+
| item      | Branch 1 | Branch 2 | Branch 3 | Branch 4 | Branch 5 | Branch 6 | Total Qty |
+-----------+----------+----------+----------+----------+----------+----------+-----------+
| Product 1 |       12 |        0 |       67 |        1 |        0 |        0 |        80 |
| Product 2 |        3 |        0 |        0 |        0 |        1 |        0 |         4 |
| Product 3 |        1 |        4 |        0 |        0 |        0 |        0 |         5 |
| Product 4 |        2 |        0 |        5 |        0 |        0 |        0 |         7 |
| Product 5 |        0 |        0 |        0 |        0 |        0 |        3 |         3 |
+-----------+----------+----------+----------+----------+----------+----------+-----------+

You asked how to make this "dynamic" because you get new products and new branches regularly.

SQL requires that all the columns of the select-list are fixed at the time the query is parsed. You cannot make any SQL query that expands its list of columns dynamically, based on the number of distinct values it discovers during execution, as it examines the data.

Trying to make a query that expands its select-list based on results of the query is like writing a PHP function whose number of arguments depends on the return value of the function! In other words, it's a chicken-and-egg problem. You have to define the function before you call it, and the definition of the function depends on the result returned by that function. This makes no sense.

For this reason, all pivot-table style queries must be done in two separate SQL queries:

1. Get a list of the distinct values you want to pivot into columns. For example:

    select id, branch_name from tbl_branch;
   

2. Format the result of the first query into the select-list for the pivot-table query like the one I showed earlier.

If you don't want to run two SQL queries, an alternative is to not do the pivoting in SQL. Just fetch the rows as they exist in the database, and write code to "pivot" the results into a tabular presentation how you like.

Answer 2

You can myke dynamic Queries, as you never know what braches and products you will have in future

Schema (MySQL v8.0)

CREATE TABLE tbl_branch (
  `ID` INTEGER,
  `Branch_Name` VARCHAR(8)
);

INSERT INTO tbl_branch
  (`ID`, `Branch_Name`)
VALUES
  ('1', 'Branch 1'),
  ('2', 'Branch 2'),
  ('3', 'Branch3'),
  ('4', 'Branch4'),
  ('5', 'Branch5'),
  ('6', 'Branch 6');
  

CREATE TABLE tbl_product (
  `ID` INTEGER,
  `Description` VARCHAR(10)
);

INSERT INTO tbl_product
  (`ID`, `Description`)
VALUES
  ('1', 'Product 1'),
  ('2', 'Product2'),
  ('3', 'Product 3'),
  ('4', 'Product 4'),
  ('5', 'Product 5'),
  ('6', 'Product 6'),
  ('7', 'Product 7'),
  ('8', 'Product 8'),
  ('9', 'Product 9'),
  ('10', 'Product 10');

CREATE TABLE tbl_orders (
  `ID` INTEGER,
  `Branch_ID` INTEGER,
  `Item_ID` INTEGER,
  `Qty` INTEGER
);

INSERT INTO tbl_orders
  (`ID`, `Branch_ID`, `Item_ID`, `Qty`)
VALUES
  ('1', '1', '1', '12'),
  ('2', '1', '2', '3'),
  ('3', '2', '3', '4'),
  ('4', '3', '4', '5'),
  ('5', '3', '1', '67'),
  ('6', '4', '1', '1'),
  ('7', '5', '2', '1'),
  ('8', '1', '3', '1'),
  ('9', '1', '4', '2'),
  ('10', '6', '5', '3');

---

Query #1

SELECT 
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE o.`Branch_ID` WHEN "',
     o.`Branch_ID`,
      '" THEN o.`Qty` ELSE 0 END) AS `',
      b.`Branch_Name`, '`'      
    )
    ORDER BY o.`Branch_ID`
  ) 
  INTO @sql
FROM tbl_product as p
JOIN tbl_orders as o on p.ID = o.Item_ID
JOIN tbl_branch as b on o.Branch_ID = b.ID;
SET @sql = CONCAT('SELECT p.Description as item,',@sql,
                  ', SUM(o.Qty) as `Total Qty` FROM tbl_product as p
JOIN tbl_orders as o on p.ID = o.Item_ID
JOIN tbl_branch as b on o.Branch_ID = b.ID GROUP BY p.id,p.Description;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

| item      | Branch 1 | Branch 2 | Branch3 | Branch4 | Branch5 | Branch 6 | Total Qty |
| --------- | -------- | -------- | ------- | ------- | ------- | -------- | --------- |
| Product 1 | 12       | 0        | 67      | 1       | 0       | 0        | 80        |
| Product2  | 3        | 0        | 0       | 0       | 1       | 0        | 4         |
| Product 3 | 1        | 4        | 0       | 0       | 0       | 0        | 5         |
| Product 4 | 2        | 0        | 5       | 0       | 0       | 0        | 7         |
| Product 5 | 0        | 0        | 0       | 0       | 0       | 3        | 3         |

---

View on DB Fiddle