我的桌子看起来像这样.

 +---------+---------------------
| ELEMENT_NAME | ELEMENT_VALUE | 
+--------------+---------------+
| NAME         | NAME1         |
| Address      | Address1      |
| City         | City1         |
| NAME         | NAME2         |
| Address      | Address2      |
| City         | City1         |
+-------------------------------

我需要这样的输出

+---------+---------------------
| NAME  |Address    | City     |
+--------------+---------------+
| NAME1 | Address1  | City1    |
| NAME2 | Address2  | City2    |
+-------------------------------

注意：-名称,地址,城市仅是示例.可以是任何东西

有人可以帮我吗？

解决方法:

您可以为此使用GROUP_CONCAT()：

SELECT ID
   ,GROUP_CONCAT(CASE WHEN ELEMENT_NAME = 'NAME' 
                 THEN ELEMENT_VALUE ELSE NULL END) AS `NAME`
   ,GROUP_CONCAT(CASE WHEN ELEMENT_NAME = 'Address' 
                 THEN ELEMENT_VALUE ELSE NULL END) AS `Address`
   ,GROUP_CONCAT(CASE WHEN ELEMENT_NAME = 'City' 
                 THEN ELEMENT_VALUE ELSE NULL END) AS `City`
  FROM Table1
 GROUP BY ID;

动态查询(如果您不知道ELEMENT_NAME的数目或太多ELEMENT_NAME)：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT(CASE WHEN `ELEMENT_NAME` = ''',
      `ELEMENT_NAME`,
      ''' THEN ELEMENT_VALUE ELSE NULL END) AS `',
      `ELEMENT_NAME`, '`'
    )
  ) INTO @sql
FROM Table1;

SET @sql = CONCAT('SELECT ID, ', @sql,'
                     FROM Table1
                    GROUP BY ID
                  ');

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

输出：

| ID |  NAME |  ADDRESS |  CITY |
---------------------------------
|  1 | NAME1 | Address1 | City1 |
|  2 | NAME2 | Address2 | City1 |

查看this SQLFiddle