mysql 使用列号和行号的数据透视表

Question

我陷入了这种情况，我需要使用表列中的行号和列号值来导出下面提到的输出。我已经尝试了一切 - if/else、case when/then 但没有帮助。

非常感谢任何帮助/建议!

这是数据库表中存在的模拟示例数据 -

+--------+--------+--------+----------+-------------+ 
| Record | ColNbr | RowNbr | ColTitle | CellContent | 
+--------+--------+--------+----------+-------------+
|      1 | 1      | 1      | Unit     | sqf         |
|      1 | 1      | 2      | Unit     | cm          |
|      1 | 2      | 1      | Desc     | roof        |
|      1 | 2      | 2      | Desc     | rod         |
|      1 | 3      | 1      | Material | concrete    |
|      1 | 3      | 2      | Material | steel       |
|      1 | 4      | 1      | Quantity | 100         |
|      1 | 4      | 2      | Quantity | 12          |
|      1 | 1      | 1      | Unit     | liter       |
|      1 | 1      | 2      | Unit     | ml          |
|      1 | 2      | 1      | Desc     | bowl        |
|      1 | 2      | 2      | Desc     | plate       |
|      1 | 3      | 1      | Material | plastic     |
|      1 | 3      | 2      | Material | glass       |
|      1 | 4      | 1      | Quantity | 2           |
|      1 | 4      | 2      | Quantity | 250         |
+--------+--------+--------+----------+-------------+

预期输出 -

+--------+--------+--------+----------+-------------+ 
| Record | Unit   | Desc   | Material | Quantity    | 
+--------+--------+--------+----------+-------------+
|      1 | sqf    | roof   | concrete | 100         |
|      1 | cm     | rod    | steel    | 12          |
|      2 | liter  | bowl   | plastic  | 2           |
|      2 | ml     | plate  | glass    | 250         |
+--------+--------+--------+----------+-------------+

Answer 1

如果你的实际数据是这样的，我建议你考虑将数据分离到；例如，4 个不同的表(单位、描述、 Material 和一个用于存储所有 ID+数量的表)。前 3 个表将存储随时间推移进行较小更新的先决条件信息，最后一个表将存储所有数量记录。假设您的表格如下所示:

CREATE TABLE `Unit` (
unit_id INT,
unit_name VARCHAR(50));

+---------+-----------+
| unit_id | unit_name |
+---------+-----------+
|    1    | sqf       |
|    2    | cm        |
|    3    | liter     |
|    4    | ml        |
+---------+-----------+

CREATE TABLE `Description` (
desc_id INT,
desc_name VARCHAR(50));

+---------+-----------+
| desc_id | desc_name |
+---------+-----------+
|    1    | roof      |
|    2    | rod       |
|    3    | bowl      |
|    4    | plate     |
+---------+-----------+

CREATE TABLE `Material` (
mat_id INT,
mat_name VARCHAR(50));

+--------+----------+
| mat_id | mat_name |
+--------+----------+
|    1   | concrete |
|    2   | steel    |
|    3   | plastic  |
|    4   | glass    |
+--------+----------+

CREATE TABLE `Records` (
unit_id INT,
desc_id INT,
mat_id INT,
quantity DECIMAL(14,4));

+---------+---------+--------+----------+ 
| unit_id | desc_id | mat_id | Quantity | 
+---------+---------+--------+----------+
|    1    |    1    |    1   | 100      |
|    2    |    2    |    2   | 12       |
|    3    |    3    |    3   | 2        |
|    4    |    4    |    4   | 250      |
+---------+---------+--------+----------+

然后相应地插入数据。

无论如何，对于您现有的数据示例，这是可以完成的，但有人担心单位+描述+ Material +数量匹配是否正确。我至少可以认为它正确匹配的唯一方法是为所有查询提供类似的 ORDER BY 子句。因此，如下:

SELECT A.record,A.unit,B.Desc,C.Material,D.Quantity FROM
     (SELECT @rn:=@rn+1 AS record,CASE WHEN coltitle='unit' THEN cellcontent END AS Unit 
        FROM yourtable, (SELECT @rn :=0 ) v 
      HAVING unit IS NOT NULL
      ORDER BY colnbr) A LEFT JOIN
     (SELECT @rn1:=@rn1+1 AS record,CASE WHEN coltitle='Desc' THEN cellcontent END AS `Desc`
        FROM yourtable, (SELECT @rn1 :=0 ) v 
      HAVING `Desc` IS NOT NULL
      ORDER BY colnbr) B ON a.record=b.record LEFT JOIN 
     (SELECT @rn2:=@rn2+1 AS record,CASE WHEN coltitle='material' THEN cellcontent END AS Material 
        FROM yourtable, (SELECT @rn2:=0 ) v 
      HAVING Material IS NOT NULL
      ORDER BY colnbr) C ON a.record=c.record LEFT JOIN 
     (SELECT @rn3:=@rn3+1 AS record,CASE WHEN coltitle='Quantity' THEN cellcontent END AS Quantity 
        FROM yourtable, (SELECT @rn3:=0 ) v 
      HAVING Quantity IS NOT NULL
      ORDER BY colnbr) D ON a.record=d.record;

这里的想法是基于COLTITLE创建一个子查询，然后分配一个编号/排名(@rn,@rn1,@rn2,@rn3)变量到每个子查询并使用 LEFT JOIN 将它们连接起来。现在，这个实验可以准确地返回您需要的输出，但它不是一个明确的答案，因为有些部分是有问题的，特别是在正确匹配组合方面。希望这会给您一些想法。