如果您同时具有order和item的已知数量的值，则可以将查询硬编码为:

If you had a known number of values for both order and item, then you could hard code the query into:

select id,
  max(case when `order` = 1 then data end) order1,
  max(case when `order` = 2 then data end) order2,
  max(case when `order` = 3 then data end) order3,
  max(case when item = 1 then price end) item1,
  max(case when item = 2 then price end) item2,
  max(case when item = 3 then price end) item3,
  max(case when item = 4 then price end) item4
from tableA
group by id;

请参见演示.但是您将要遇到的部分问题是因为您试图转换多列数据.我建议获得最终结果的方法是先取消数据透视. MySQL没有取消透视功能，但是您可以使用UNION ALL将多对列转换为行.要取消透视的代码将类似于以下内容:

See Demo. But part of the problem that you are going to have is because you are trying to transform multiple columns of data. My suggestion to get the final result would be to unpivot the data first. MySQL does not have an unpivot function but you can use a UNION ALL to convert the multiple pairs of columns into rows. The code to unpivot will be similar to the following:

select id, concat('order', `order`) col,  data value
from tableA
union all
select id, concat('item', item) col, price value
from tableA;

请参见演示.结果将是:

| ID |    COL | VALUE |
-----------------------
|  1 | order1 |     P |
|  1 | order1 |     P |
|  1 | order1 |     P |
|  1 |  item1 |    50 |
|  1 |  item2 |    60 |
|  1 |  item3 |    70 |

如您所见，这已经占用了order/data和item/price的多列，并将其转换为多行.完成后，您可以使用带有CASE的聚合函数将值转换回列:

As you can see this has taken the multiple columns of order/data and item/price and convert it into multiple rows. Once that is completed, then you can convert the values back into columns using an aggregate function with a CASE:

select id, 
  max(case when col = 'order1' then value end) order1,
  max(case when col = 'order2' then value end) order2,
  max(case when col = 'order3' then value end) order3,
  max(case when col = 'item1' then value end) item1,
  max(case when col = 'item2' then value end) item2,
  max(case when col = 'item3' then value end) item3
from
(
  select id, concat('order', `order`) col,  data value
  from tableA
  union all
  select id, concat('item', item) col, price value
  from tableA
) d
group by id;

请参见演示.最后，您需要将以上代码转换为动态的预准备语句查询:

See Demo. Finally, you need to convert the above code into a dynamic prepared statement query:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when col = ''',
      col,
      ''' then value end) as `', 
      col, '`')
  ) INTO @sql
FROM
(
  select concat('order', `order`) col
  from tableA
  union all
  select concat('item', `item`) col
  from tableA
)d;

SET @sql = CONCAT('SELECT id, ', @sql, ' 
                  from
                  (
                    select id, concat(''order'', `order`) col,  data value
                    from tableA
                    union all
                    select id, concat(''item'', item) col, price value
                    from tableA
                  ) d
                  group by id');

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

请参见带演示的SQL Fiddle .得到的结果是:

See SQL Fiddle with demo. This gives a result:

| ID | ORDER1 | ORDER2 | ORDER3 | ITEM1 | ITEM2 |  ITEM3 |  ITEM4 |
-------------------------------------------------------------------
|  1 |      P |      Q | (null) |    50 |    60 |     70 | (null) |
|  2 |      P | (null) |      S |    50 |    60 | (null) |     80 |