sql - MySQL查询帮助: how to pivot this table?

Question

给定一个这样的表:

==============================================
| ID1 | ID2 | ID3 | Name          | Value    |
==============================================
| 16  | 1   | 100 | item_name     | Toys     |
| 16  | 2   | 101 | item_name     | Computer |
| 16  | 1   | 102 | item_price    | 55       |
| 16  | 2   | 103 | item_price    | 200      |
| 16  | 1   | 104 | animal_name   | dog      |
| 16  | 2   | 105 | animal_name   | cat      |
| 16  | 1   | 106 | animal_gender | male     |
| 16  | 2   | 107 | animal_gender | female   |
| 18  | 1   | 100 | item_name     | Toys     |
| 18  | 2   | 101 | item_name     | Computer |
| 18  | 1   | 102 | item_price    | 55       |
| 18  | 2   | 103 | item_price    | 200      |
| 18  | 1   | 104 | animal_name   | dog      |
| 18  | 2   | 105 | animal_name   | cat      |
| 18  | 1   | 106 | animal_gender | male     |
| 18  | 2   | 107 | animal_gender | female   |
----------------------------------------------

如何使用 SQL 使其成为这样:

==============================================================
| ID1 | item_name | item_price | animal_name | animal_gender |
==============================================================
| 16  | Toys      | 55         | dog         | male          |
| 16  | Toys      | 55         | cat         | female        |
| 16  | Computer  | 200        | dog         | male          |
| 16  | Computer  | 200        | cat         | female        |
| 18  | Toys      | 55         | dog         | male          |
| 18  | Toys      | 55         | cat         | female        |
| 18  | Computer  | 200        | dog         | male          |
| 18  | Computer  | 200        | cat         | female        |
--------------------------------------------------------------

我将在 PHP 中创建 SQL 查询。

Answer 1

已编辑，遵循 SONewbie 的评论

应符合 ANSI 标准:

SELECT  
  ID1,  
  MAX(CASE WHEN Name = 'item_name' THEN Value ELSE NULL END) AS item_name, 
  MAX(CASE WHEN Name = 'item_price' THEN Value ELSE NULL END) AS item_price, 
  MAX(CASE WHEN Name = 'animal_name' THEN Value ELSE NULL END) AS animal_name, 
  MAX(CASE WHEN Name = 'animal_gender' THEN Value ELSE NULL END) AS animal_gender
FROM tbl 
GROUP BY ID1, ID2;

这不会产生完全所需的输出 - 相反，输出将如下所示:

==============================================================
| ID1 | item_name | item_price | animal_name | animal_gender |
==============================================================
| 16  | Toys      | 55         | dog         | male          |
| 18  | Toys      | 55         | dog         | male          |
| 16  | Computer  | 200        | cat         | female        |
| 18  | Computer  | 200        | cat         | female        |
--------------------------------------------------------------

这是因为 ID1 和 ID2 仅将猫和女性与计算机和 200 联系起来，而相同的字段仅将狗和男性与玩具和 55 联系起来。按 ID3 分组会引入大量 NULL，因为 ID1 和 ID3 一起唯一标识数据行。