在 Oracle 10g 中展平一对多关系中的列

发布于 2024-09-29 08:56:52 字数 943 浏览 4 评论 0原文

我猜这是一个弄清楚要使用什么 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

冰雪之触 2024-10-06 08:56:52

Here is an explanation with sample code using the collect_func()

http://www.adp-gmbh.ch/blog/2005/march/28.html

策马西风 2024-10-06 08:56:52

阅读有关 Oracle 中字符串连接的页面

假设可用,您可以使用 wm_concat:

  SELECT a.prjid,
         wm_concat(b.userid) AS userids
    FROM TABLE_1 a
    JOIN TABLE_2 b ON b.prjid = a.prjid
GROUP BY a.prjid

Read this page about String Concatenation in Oracle.

Assuming available, you can use wm_concat:

  SELECT a.prjid,
         wm_concat(b.userid) AS userids
    FROM TABLE_1 a
    JOIN TABLE_2 b ON b.prjid = a.prjid
GROUP BY a.prjid
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文