如何在mySQL中组合几行记录

Question

我正在尝试组合几行，组合并输出为一行。以下是我的示例和预期输出。我尝试过group_concat并将if/case条件放入查询中，但它似乎无法按预期输出。提前谢谢。

Data:
id | Type   | Name       |Supermarket
1    Fruit    Apple       East
2    Fruit    Orange      West
3    Fruit    Pear        Central
Expected Output:
Type   |East    |West      |Central
Fruit   Apple    Orange     Pear

Answer 1

我查找的重复问题没有很清楚的答案IMHO，所以我会写一个：

首先，我们需要将感兴趣的数据分布在3列中：

SELECT
type,
CASE WHEN supermarket = 'east' THEN name END as east,
CASE WHEN supermarket = 'west' THEN name END as west,
CASE WHEN supermarket = 'central' THEN name END as central
FROM t

运行它，您将看到数据分布到3行3列，大部分为null。现在我们需要去掉null。如果我们在每个有null的列上使用MAX((，我们将隐藏除一个有值的单元格之外的所有内容(MAX将始终在null上选择一个值(

SELECT
type,
MAX(CASE WHEN supermarket = 'east' THEN name END) as east,
MAX(CASE WHEN supermarket = 'west' THEN name END) as west,
MAX(CASE WHEN supermarket = 'central' THEN name END) as central
FROM t
GROUP BY type