在 Oracle 10g 中展平一对多关系中的列
我猜这是一个弄清楚要使用什么 oracle 命令的问题,但是经过几个小时的谷歌搜索,我还没有找到任何可以完成我需要的事情。简而言之,这就是我需要发生的事情:
Table 1 Table 2 | PrjID | | PrjID | UserID | |----------| |----------|----------| | Project1 | | Project1 | User1 | | Project2 | | Project1 | User2 | | Project3 | | Project1 | User3 | | Project4 | | Project2 | User2 | | Project5 | | Project3 | User5 |
我需要生成一个 SQL 查询,以便使用上面的两个表,我可以生成如下所示的结果:
| PrjID | UserIDs | |----------|-------------------| | Project1 | User1,User2,User3 | | Project2 | User2 | | Project3 | User5 |
我想尝试的第一件事是 join 语句,但是我相信你们很多人都知道,这将给出每个项目都有多行的结果。我还遇到了一些关于枢轴的教程,但这实际上只是为了将结果翻转过来,而不是实际聚合行的字符串值。我对 SQL 的更高级部分仍然有点陌生,所以很可能我只是在谷歌上搜索错误的内容来找出如何做到这一点。我可能可以用 Java 编写一个解决方案(这是 web 应用程序的一部分),它只会迭代行并提取每个用户,但我真的很想学习一种让 SQL 为我完成这部分工作的方法。
任何提供的帮助将不胜感激!
I'm guessing this is a matter of figuring out what oracle command to use, but after a couple of hours of googling, I haven't found anything that can do what I need. So here's what I need to have happen in a nutshell:
Table 1 Table 2 | PrjID | | PrjID | UserID | |----------| |----------|----------| | Project1 | | Project1 | User1 | | Project2 | | Project1 | User2 | | Project3 | | Project1 | User3 | | Project4 | | Project2 | User2 | | Project5 | | Project3 | User5 |
I need to generate an SQL query such that with the above two tables, I can generate a result that looks like this:
| PrjID | UserIDs | |----------|-------------------| | Project1 | User1,User2,User3 | | Project2 | User2 | | Project3 | User5 |
The first thing I thought to try was a join statement, but as I'm sure many of you are aware, this will give a result that has multiple rows for each project. I also came across several tutorials on pivots, but that is really for just turning your result on its side and not about actually aggregating a row's string values. I'm still a bit new to the more advanced parts of SQL, so it's very possible I'm just googling the wrong things to figure out how to do this. I can probably hack together a solution in Java (this is part of a webapp) that would just iterate through the rows and pull out every user, but I'd really like to learn a way to make SQL do that part for me.
Any help offered would be very appreciated!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这是使用collect_func()
http://www 的 示例代码的解释.adp-gmbh.ch/blog/2005/march/28.html
Here is an explanation with sample code using the collect_func()
http://www.adp-gmbh.ch/blog/2005/march/28.html
阅读有关 Oracle 中字符串连接的页面。
假设可用,您可以使用 wm_concat:
Read this page about String Concatenation in Oracle.
Assuming available, you can use wm_concat: