我试着搜索帖子，但我只找到SQL Server/Access的解决方案。我需要一个解决方案在MySQL (5.X)。
我有一个表(称为历史)3列:hostid, itemname, itemvalue。
如果我执行select (select * from history)，它会返回
+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
| 1 | A | 10 |
+--------+----------+-----------+
| 1 | B | 3 |
+--------+----------+-----------+
| 2 | A | 9 |
+--------+----------+-----------+
| 2 | C | 40 |
+--------+----------+-----------+
如何查询数据库以返回类似的内容
+--------+------+-----+-----+
| hostid | A | B | C |
+--------+------+-----+-----+
| 1 | 10 | 3 | 0 |
+--------+------+-----+-----+
| 2 | 9 | 0 | 40 |
+--------+------+-----+-----+
这不是你正在寻找的确切答案，但这是一个解决方案，我需要在我的项目，希望这有助于某人。这将列出用逗号分隔的1到n行项目。Group_Concat使这在MySQL中成为可能。
select
cemetery.cemetery_id as "Cemetery_ID",
GROUP_CONCAT(distinct(names.name)) as "Cemetery_Name",
cemetery.latitude as Latitude,
cemetery.longitude as Longitude,
c.Contact_Info,
d.Direction_Type,
d.Directions
from cemetery
left join cemetery_names on cemetery.cemetery_id = cemetery_names.cemetery_id
left join names on cemetery_names.name_id = names.name_id
left join cemetery_contact on cemetery.cemetery_id = cemetery_contact.cemetery_id
left join
(
select
cemetery_contact.cemetery_id as cID,
group_concat(contacts.name, char(32), phone.number) as Contact_Info
from cemetery_contact
left join contacts on cemetery_contact.contact_id = contacts.contact_id
left join phone on cemetery_contact.contact_id = phone.contact_id
group by cID
)
as c on c.cID = cemetery.cemetery_id
left join
(
select
cemetery_id as dID,
group_concat(direction_type.direction_type) as Direction_Type,
group_concat(directions.value , char(13), char(9)) as Directions
from directions
left join direction_type on directions.type = direction_type.direction_type_id
group by dID
)
as d on d.dID = cemetery.cemetery_id
group by Cemetery_ID
这个墓地有两个公共名称，因此名称被列在不同的行中，由一个id连接，但有两个名称id，查询产生如下内容
CemeteryID Cemetery_Name 纬度
1 阿普尔顿,Sulpher弹簧35.4276242832293
利用Matt Fenwick的想法帮助我解决了这个问题(非常感谢)，让我们把它简化为一个问题:
select
history.*,
coalesce(sum(case when itemname = "A" then itemvalue end), 0) as A,
coalesce(sum(case when itemname = "B" then itemvalue end), 0) as B,
coalesce(sum(case when itemname = "C" then itemvalue end), 0) as C
from history
group by hostid
这不是你正在寻找的确切答案，但这是一个解决方案，我需要在我的项目，希望这有助于某人。这将列出用逗号分隔的1到n行项目。Group_Concat使这在MySQL中成为可能。
select
cemetery.cemetery_id as "Cemetery_ID",
GROUP_CONCAT(distinct(names.name)) as "Cemetery_Name",
cemetery.latitude as Latitude,
cemetery.longitude as Longitude,
c.Contact_Info,
d.Direction_Type,
d.Directions
from cemetery
left join cemetery_names on cemetery.cemetery_id = cemetery_names.cemetery_id
left join names on cemetery_names.name_id = names.name_id
left join cemetery_contact on cemetery.cemetery_id = cemetery_contact.cemetery_id
left join
(
select
cemetery_contact.cemetery_id as cID,
group_concat(contacts.name, char(32), phone.number) as Contact_Info
from cemetery_contact
left join contacts on cemetery_contact.contact_id = contacts.contact_id
left join phone on cemetery_contact.contact_id = phone.contact_id
group by cID
)
as c on c.cID = cemetery.cemetery_id
left join
(
select
cemetery_id as dID,
group_concat(direction_type.direction_type) as Direction_Type,
group_concat(directions.value , char(13), char(9)) as Directions
from directions
left join direction_type on directions.type = direction_type.direction_type_id
group by dID
)
as d on d.dID = cemetery.cemetery_id
group by Cemetery_ID
这个墓地有两个公共名称，因此名称被列在不同的行中，由一个id连接，但有两个名称id，查询产生如下内容
CemeteryID Cemetery_Name 纬度
1 阿普尔顿,Sulpher弹簧35.4276242832293
如果你可以使用MariaDB，有一个非常非常简单的解决方案。
自MariaDB-10.02以来，添加了一个新的存储引擎CONNECT，可以帮助我们将另一个查询或表的结果转换为数据透视表，就像你想要的那样:
你可以看看这些文件。
首先安装connect存储引擎。
现在我们的表的主列是itemname，每一项的数据都位于itemvalue列中，所以我们可以使用这个查询得到结果数据透视表:
create table pivot_table
engine=connect table_type=pivot tabname=history
option_list='PivotCol=itemname,FncCol=itemvalue';
现在我们可以从数据透视表中选择我们想要的:
select * from pivot_table
详情请点击这里
