It seems a MySQL View will not be able to utilize a prepared statement or a stored procedure.
Build query string Approach
You might could use two queries where one provides the list of staff and the second provides the generated-on-the-fly table B.
Get your staff list:
SELECT staff FROM A GROUP BY staff
Make string
Then loop through each staff member and generate this string for each staff:
(SELECT sum(A2.time) FROM A as A2 WHERE date=A1.date AND staff="<staff>") AS <staff>,
Query
Then having generated this string use it in this query:
SELECT A1.date,
<insert-generated-query-string-of-staff>
sum(time)
FROM A as A1 GROUP BY date;
Which could look like this generated:
SELECT A1.date,
(SELECT SUM(A2.time) FROM A as A2 WHERE date=A1.date AND staff="John") AS John,
(SELECT SUM(A2.time) FROM A as A2 WHERE date=A1.date AND staff="Jeff") AS Jeff,
(SELECT SUM(A2.time) FROM A as A2 WHERE date=A1.date AND staff="James") AS James,
SUM(A1.time)
FROM A as A1 GROUP BY date;
Prepared Statement Approach
Similar to the previous approach except you're building the query strings through MySQL instead of your program. These will need to be executed every time you want to generate the table data.
SELECT GROUP_CONCAT( DISTINCT CONCAT(
"(SELECT SUM(A2.time) FROM A as A2 WHERE date=A1.date AND staff=\"",
staff,
"\") AS ",
staff
) ) INTO @staff from A;
SET @table_b = CONCAT(
'SELECT A1.date, ',
@staff,
', SUM(A1.time) FROM A as A1 GROUP BY date'
);
PREPARE tableB from @table_b;
EXECUTE tableB;
Stored Procedure Approach
I came across some other ideas mostly with examples of prepared statements
and/or stored procedures and came up with this procedure.
I think this one is better because you don't have to re-create query like
prepared statements.
To make the procedure:
DROP PROCEDURE generate_table_B;
DELIMITER //
CREATE PROCEDURE generate_table_B()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE staff_name VARCHAR(50) DEFAULT NULL;
DECLARE staff_list CURSOR FOR SELECT staff FROM A GROUP BY staff;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN staff_list;
SET @staff_stmts = '';
get_id: LOOP
FETCH staff_list INTO staff_name;
IF done = 1 THEN
LEAVE get_id;
END IF;
SET @staff_stmts = CONCAT(@staff_stmts, ' (SELECT SUM(A2.time) FROM A as A2 WHERE date=A1.date AND staff="', staff_name,'") AS ', staff_name,',');
END LOOP get_id;
CLOSE staff_list;
SET @table = CONCAT('SELECT A1.date, ', @staff_stmts, ' SUM(A1.time) FROM A as A1 GROUP BY date');
-- verifying the @table string generated
-- select @table;
PREPARE stmt FROM @table;
-- run @table
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
The statement to call in the program/script:
call generate_table_B();
Aliases
These are specified right after a column name, calculation, query, or table.
It is optional to use AS after one of these.
The reason is because of the nested queries. In this case if we did not use
aliases the inner query would be making comparisons from its query with data
from the outside query. Depending on the situation this may be the desired
result. However, our query below shows it is not what we want.
> SELECT date,
-> (SELECT SUM(time) FROM A WHERE date=date AND staff="John") AS John,
-> (SELECT SUM(time) FROM A WHERE date=date AND staff="Jeff") AS Jeff,
-> (SELECT SUM(time) FROM A WHERE date=date AND staff="James") AS James,
-> SUM(time)
-> FROM A GROUP BY date;
+------+------+------+-------+-----------+
| date | John | Jeff | James | SUM(time) |
+------+------+------+-------+-----------+
| 1 | 9 | 8 | 4 | 10 |
| 2 | 9 | 8 | 4 | 11 |
+------+------+------+-------+-----------+
2 rows in set (0.01 sec)
Now you may notice there's the column SUM(time). This qualifies as a
calculation so we can set an alias for this column so it's friendlier to use
when selecting it. Like this:
> SELECT date,
-> (SELECT SUM(time) FROM A WHERE date=date AND staff="John") AS John,
-> (SELECT SUM(time) FROM A WHERE date=date AND staff="Jeff") AS Jeff,
-> (SELECT SUM(time) FROM A WHERE date=date AND staff="James") AS James,
-> SUM(time) SummedTime
-> FROM A GROUP BY date;
+------+------+------+-------+------------+
| date | John | Jeff | James | SummedTime |
+------+------+------+-------+------------+
| 1 | 9 | 8 | 4 | 10 |
| 2 | 9 | 8 | 4 | 11 |
+------+------+------+-------+------------+
2 rows in set (0.01 sec)
Output from all 3 approaches:
+------+-------+------+------+--------------+
| date | James | jeff | john | SUM(A1.time) |
+------+-------+------+------+--------------+
| 1 | 2 | 3 | 5 | 10 |
| 2 | 2 | 5 | 4 | 11 |
+------+-------+------+------+--------------+
2 rows in set (0.00 sec)
MySQL table & data:
> show create table A\G
*************************** 1. row ***************************
Table: A
Create Table: CREATE TABLE `A` (
`date` int(11) DEFAULT NULL,
`staff` varchar(50) DEFAULT NULL,
`tp` varchar(50) DEFAULT NULL,
`time` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
> SELECT * FROM A;
+------+-------+-------+------+
| date | staff | tp | time |
+------+-------+-------+------+
| 1 | john | bus | 3 |
| 1 | jeff | car | 3 |
| 1 | James | Train | 2 |
| 2 | Jeff | bus | 5 |
| 2 | john | car | 4 |
| 2 | james | train | 2 |
| 1 | john | train | 2 |
+------+-------+-------+------+
7 rows in set (0.00 sec)
sources:
