SQL 查询 - 不同的还是唯一的？

Question

SQL 新手，有一个查询通过多个表中的多个连接提取一些数据。

示例数据:

PERSON_NAME FUNCTION_NAME  FUNCTION_ID  FUNCTION_GROUP

Bob         View Result    1            Editor
Bob         Edit Result    4            Editor
Bob         Delete Result  3            Editor
Bob         Email Result   8            Editor
Mary        Print Letter   45           Admin
Mary        Grant Access   37           Admin

函数有 ID，function_groups 有很多函数。我想查询数据，而不是它看起来像上面的例子，它看起来像:

PERSON_NAME FUNCTION_NAME                             FUNCTION_ID     FUNCTION_GROUP

Bob         View Result,Edit Result, Delete Result   1,4,3,8          Editor
Mary        Print Letter,Grant Access                 45,37           Admin

“Bob 属于 Editor，editor 具有以下功能”作为一个结果，而不是初始示例，其中返回多行。

我认为独特或独特的关键字可以帮助我的想法是否正确？谢谢!

编辑:现在有了代码

select staff_member.person_name, function.function_name,staff_group_function.function_id, staff_group.function_group_name
 from staff_member
 inner join staff_group
 on staff_group.staff_group_id=staff_group_member.staff_group_id
 inner join staff_group_function
 on staff_group_function.staff_group_id=staff_group_member.staff_group_id
 inner join function
 on function.function_id=staff_group_function.function_group_name

Answer 1

没有。你需要的是LISTAGG()如果你有 Oracle 11g2。这可能是您的查询:

SELECT person_name,
       LISTAGG(function_name, ', ') WITHIN GROUP (ORDER BY 1) function_name,
       LISTAGG(function_id,   ', ') WITHIN GROUP (ORDER BY 1) function_id,
       function_group
FROM my_table
GROUP BY person_name, function_group

或者(根据您的最新评论):

SELECT person_name,
       LISTAGG(function_name,  ', ') WITHIN GROUP (ORDER BY 1) function_name,
       LISTAGG(function_id,    ', ') WITHIN GROUP (ORDER BY 1) function_id,
       LISTAGG(function_group, ', ') WITHIN GROUP (ORDER BY 1) function_group
FROM my_table
GROUP BY person_name

对于 11g2 之前的任何版本，这篇有趣的文章为您提供了解决方案:

http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php