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

CREATE EXTENSION tablefunc;

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

CREATE EXTENSION hstore;

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

我把它变成Group By hostId，然后它只会显示第一行的值， 如:

A   B  C
1  10
2      3

你可以使用几个LEFT join。请使用此代码

SELECT t.hostid,
       COALESCE(t1.itemvalue, 0) A,
       COALESCE(t2.itemvalue, 0) B,
       COALESCE(t3.itemvalue, 0) C 
FROM history t 
LEFT JOIN history t1 
    ON t1.hostid = t.hostid 
    AND t1.itemname = 'A' 
LEFT JOIN history t2 
    ON t2.hostid = t.hostid 
    AND t2.itemname = 'B' 
LEFT JOIN history t3 
    ON t3.hostid = t.hostid 
    AND t3.itemname = 'C' 
GROUP BY t.hostid

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;

另一个选择，尤其有用，如果你有很多项，你需要枢轴是让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

我的解决方案:

select h.hostid, sum(ifnull(h.A,0)) as A, sum(ifnull(h.B,0)) as B, sum(ifnull(h.C,0)) as  C from (
select
hostid,
case when itemName = 'A' then itemvalue end as A,
case when itemName = 'B' then itemvalue end as B,
case when itemName = 'C' then itemvalue end as C
  from history 
) h group by hostid

它在提交的案例中产生预期的结果。