将多个视图透视到单个结果表/vi中

Question

我有以下两种看法：

实验：

select * from experiments;
+--------+--------------------+-----------------+
| exp_id | exp_properties     | value           |
+--------+--------------------+-----------------+
|      1 | indicator:chemical | phenolphthalein |
|      1 | base               | NaOH            |
|      1 | acid               | HCl             |
|      1 | exp_type           | titration       |
|      1 | indicator:color    | faint_pink      |
+--------+--------------------+-----------------+

计算：

select * from calculations;
+--------+------------------------+--------------+
| exp_id | exp_report             | value        |
+--------+------------------------+--------------+
|      1 | molarity:base          |  0.500000000 |
|      1 | volume:acid:in_ML      | 23.120000000 |
|      1 | volume:base:in_ML      |  5.430000000 |
|      1 | moles:H                |  0.012500000 |
|      1 | moles:OH               |  0.012500000 |
|      1 | molarity:acid          |  0.250000000 |
+--------+------------------------+--------------+

我设法将这些视图中的每一个单独地进行了透视，如下所示：

实验与数据透视：

+-------+--------------------+------+------+-----------+----------------+
|exp_id | indicator:chemical | base | acid | exp_type  | indicator:color|
+-------+--------------------+------+------+-----------+----------------+
|   1   | phenolphthalein    | NaOH | HCl  | titration | faint_pink     |
+------+---------------------+------+------+-----------+----------------+

计算轴：

+-------+---------------+---------------+--------------+-------------+------------------+-------------------+
|exp_id | molarity:base | molarity:acid |   moles:H    |   moles:OH  | volume:acid:in_ML| volume:base:in_ML |
+-------+---------------+---------------+--------------+-------------+------------------+-------------------+
|   1   | 0.500000000   | 0.250000000   | 0.012500000  | 0.012500000 | 23.120000000     | 5.430000000       |
+------+---------------------+------+------+-----------+----------------------------------------------------+

我的问题是如何将这两个pivot结果作为一行获得？预期结果如下：

+-------+--------------------+------+------+-----------+----------------+--------------+---------------+--------------+-------------+------------------+------------------+
|exp_id | indicator:chemical | base | acid | exp_type  | indicator:color|molarity:base | molarity:acid |   moles:H    |   moles:OH | volume:acid:in_ML| volume:base:in_ML |
+-------+--------------------+------+------+-----------+----------------+--------------+---------------+--------------+-------------+------------------+------------------+
|   1   | phenolphthalein    | NaOH | HCl  | titration |  faint_pink    | 0.500000000  | 0.250000000   | 0.012500000  | 0.012500000 | 23.120000000     | 5.430000000      |
+------+---------------------+------+------+-----------+----------------+--------------+---------------+--------------+-------------+------------------+------------------+

使用的数据库：Mysql

重要提示：这些视图中的每一个都可以有越来越多的行。因此，我分别考虑了每个视图的“动态枢轴”

仅供参考——下面是我用来在MySQL中透视experiments的一条准备好的语句（以及用于透视另一个视图的类似语句）：

set @sql = Null;
SELECT
 GROUP_CONCAT(DISTINCT
   CONCAT(
     'MAX(IF(exp_properties = ''',
    exp_properties,
    ''', value, NULL)) AS ',
    concat("`",exp_properties, "`")
   )
 )into @sql
from experiments;

set @sql = concat(
  'select exp_id, ',
  @sql,
  ' from experiment group by exp_id'
  );
prepare stmt from @sql;
execute stmt;