过滤掉重复项，但在一行中显示另一列中的非重复行

Question

我有一个包含多个重复值的表。我想要做的是使用某种SELECT查询，该查询将按名称过滤重复项，同时保留"expertise“列中的所有适当值，并将它们显示在由coma分隔的一行中。

换句话说，我想转一张桌子，看起来像这样：

 names   | organization | education_years | expertise 
---------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------
 John    | University   | 2016 – 2020     | Programming
 John    | University   | 2016 – 2020     | machine Learning
 John    | University   | 2016 – 2020     | AI
 John    | University   | 2016 – 2020     | Robotics
 John    | University   | 2016 – 2020     | Symbolic Reasoning
 Gabriel | Company      | 2015 – Present  | models/networks
 Gabriel | Company      | 2015 – Present  | AI
 Gabriel | Company      | 2015 – Present  | Speech/language
 Gabriel | Company      | 2015 – Present  | machine learning
 Gabriel | Company      | 2015 – Present  | Programming
 Nicolas | Company      | Present         | machine learning, factorization, learning theory, supervised learning

如下所示：

 names   | organization | education_years | expertise 
---------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------
 John    | University   | 2016 – 2020     | Programming, machine Learning, AI, Robotics, Symbolic Reasoning
 Gabriel | Company      | 2015 – Present  | models/networks, AI, Speech/language, machine learning, Programming
 Nicolas | Company      | Present         | machine learning, factorization, learning theory, supervised learning

我使用PostgreSQL作为我的数据库。

有人能帮上忙吗？我已经很久没有做过查询了，也找不到一个类似的问题来解决这个问题。

Answer 1

使用聚合：

select name, organization, education_years,
       string_agg(expertise, ', ')
from t
group by name, organization, education_years;

上面使用了string_agg()，因为结果看起来像一个字符串。我建议您改用数组：

select name, organization, education_years,
       array_agg(expertise)
from t
group by name, organization, education_years;