为其他必需列提供空值的透视查询

Question

我有以下表格:

create table table1 (
Id int not null auto_increment,
JobId varchar(255) not null,
FieldOrder int(11) not null,
FieldName varchar(255) not null,
FieldValue varchar(255) not null,
primary key (Id)
); 

它有以下数据:

| Id  | JobId | FieldOrder | FieldName    | FieldValue |
| --- | ----- | ---------- | ------------ | ---------- |
| 1   | 1     | 1          | Customer Id  | C01        |
| 2   | 1     | 2          | Order Number | 3923       |
| 3   | 1     | 3          | Architect Id | DK         |
| 4   | 2     | 1          | Customer Id  | C02        |
| 5   | 2     | 2          | Order Number | 23         |
| 6   | 2     | 3          | Architect Id | AJ         |
| 7   | 3     | 1          | Customer Id  | C03        |
| 8   | 3     | 2          | Plot Id      | 3          |
| 9   | 3     | 3          | Architect Id |            |

我正在尝试编写一个旋转查询，以便我可以得到以下内容:

+-------------+--------------+--------------+
| customer_id | order_number | architect_id |
+-------------+--------------+--------------+
| C01         | 3923         | DK           |
| C02         | 23           | AJ           |
| C03         |              |              |
+-------------+--------------+--------------+

<<p>当前进展/strong>到目前为止，我的查询看起来像这样:

select case
when FieldName = 'Customer Id' then FieldValue
end as "customer_id",
case
when FieldName = 'Order Number' then FieldValue
end as "order_number",
case
when FieldName = 'Architect Id' then FieldValue
end as "architect_id"
from table1
group by JobId;

在本地，它给出了以下内容:

+-------------+--------------+--------------+
| customer_id | order_number | architect_id |
+-------------+--------------+--------------+
| C01         | NULL         | NULL         |
| C02         | NULL         | NULL         |
| C03         | NULL         | NULL         |
+-------------+--------------+--------------+

我怀疑，因为当我做group by时，我不再能够访问我的字段，所以我被卡住了，我不确定我如何才能让它工作。

我也创建了一个db-fiddle，尽管由于一些默认设置它甚至不会运行。

Answer 1

你想要聚合:

select jobid,
max(case when FieldName = 'Customer Id' then FieldValue
end) as customer_id,
max(case when FieldName = 'Order Number' then FieldValue
end) as order_number,
max(case when FieldName = 'Architect Id' then FieldValue
end) as architect_id
from table1
group by JobId;

指出:

• 我将jobid添加到select中。通常，您希望区分行。
• 不要转义不需要转义的标识符。我去掉了双引号。