Oracle 相当于 SQL Server STUFF 函数吗?

2021-12-19 00:00:00 select oracle

Oracle 是否有自己的 SQL Server stuff 函数实现?

Does Oracle have its own implementation of SQL Server stuff function?

Stuff 允许您从多行选择中接收一个值.下面考虑我的情况

Stuff allows you to receive one value from a multi row select. Consider my situation below

 ID   HOUSE_REF   PERSON
 1      A         Dave
 2      A         John
 3      B         Bob

我想编写一个选择语句，但我希望 PERSON 名称在一行中.

I would like to write a select statement, but I want the PERSON names to be in a single row.

比如当我从这个表中选择时，我想实现如下

For example, when I select from this table, I want to achieve the following

HOUSE_REF   PERSONS
A           Dave, John
B           Bob

到目前为止我还没有找到一个简单的解决方案，可能是编写自己的函数来使用，但我不完全确定如何解决这个问题，有什么想法吗?

I haven't been able to find a simple solution so far, it may be a case of writing my own function to use, but I'm not entirely sure of how to approach this, any ideas?

这个的主要业务用途是有一个选择语句来显示每个房子，并针对那个房子有一个列列出住在那个房子里的每个人.这个选择中的房子引用必须是唯一的，因此需要连接人

The main business use of this, will be to have a select statement that shows each house, and against that house to have one column which lists everyone that lives in that house. The house ref in this select must be unique, hence needing to concatenate the persons

谢谢

推荐答案

您可以编写自定义聚合函数来执行此操作.您生成的此字符串限制为 4k 个字符.

You can write a custom aggregate function to do this. This string you generate is limited to 4k characters.

http://www.sqlsnippets.com/en/topic-11591.html

有一个未记录的、不受支持的函数 WMSYS.WM_CONCAT 可以做同样的事情.

There is an undocumented, unsupported function WMSYS.WM_CONCAT to do the same thing.

http://www.psoug.org/reference/undocumented.html

相关文章