mysql中的输出,mysql-将输出结果作为SQL中的列

我的桌子看起来像这样.

+---------+---------------------

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

标签：pivot,sql,mysql

来源： https://codeday.me/bug/20191122/2063628.html