利用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

我编辑阿贡Sagita的答案从子查询加入。 我不确定这两种方式有多大区别，但只是作为另一个参考。

SELECT  hostid, T2.VALUE AS A, T3.VALUE AS B, T4.VALUE AS C
FROM TableTest AS T1
LEFT JOIN TableTest T2 ON T2.hostid=T1.hostid AND T2.ITEMNAME='A'
LEFT JOIN TableTest T3 ON T3.hostid=T1.hostid AND T3.ITEMNAME='B'
LEFT JOIN TableTest T4 ON T4.hostid=T1.hostid AND T4.ITEMNAME='C'

这不是你正在寻找的确切答案，但这是一个解决方案，我需要在我的项目，希望这有助于某人。这将列出用逗号分隔的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

SELECT 
    hostid, 
    sum( if( itemname = 'A', itemvalue, 0 ) ) AS A,  
    sum( if( itemname = 'B', itemvalue, 0 ) ) AS B, 
    sum( if( itemname = 'C', itemvalue, 0 ) ) AS C 
FROM 
    bob 
GROUP BY 
    hostid;

我很抱歉这么说，也许我没有完全解决你的问题，但PostgreSQL比MySQL早10年，与MySQL相比是非常先进的，有很多方法可以轻松实现这一点。安装PostgreSQL并执行此查询

CREATE EXTENSION tablefunc;

然后瞧!这里有大量的文档:PostgreSQL: documentation: 9.1: tablefunc或this查询

CREATE EXTENSION hstore;

然后，瞧!PostgreSQL: Documentation: 9.0: hstore

另一个选择，尤其有用，如果你有很多项，你需要枢轴是让mysql为你构建查询:

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'ifnull(SUM(case when itemname = ''',
      itemname,
      ''' then itemvalue end),0) AS `',
      itemname, '`'
    )
  ) INTO @sql
FROM
  history;
SET @sql = CONCAT('SELECT hostid, ', @sql, ' 
                  FROM history 
                   GROUP BY hostid');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

小提琴 添加了一些额外的值，以看到它的工作

GROUP_CONCAT的默认值为1000，因此如果您有一个非常大的查询，请在运行它之前更改此参数

SET SESSION group_concat_max_len = 1000000;

测试:

DROP TABLE IF EXISTS history;
CREATE TABLE history
(hostid INT,
itemname VARCHAR(5),
itemvalue INT);

INSERT INTO history VALUES(1,'A',10),(1,'B',3),(2,'A',9),
(2,'C',40),(2,'D',5),
(3,'A',14),(3,'B',67),(3,'D',8);

  hostid    A     B     C      D
    1     10      3     0      0
    2     9       0    40      5
    3     14     67     0      8