Join two tables in Oracle concatenating results by id

Question

I want to join two tables in Oracle and list the results concatenated if they have the same parent id in this way:

Table All

|id    | other fields|service_id|
|------|-------------|----------|
| 827  |xxxxxxxx     |null      |
| 828  |xxxxxxxx     |327       |
| 829  |xxxxxxxx     |328       |
| 860  |xxxxxxxx     |null      |
| 861  |xxxxxxxx     |326       |

Table Services

| id    | parent_id |
| ----  | -------|
|  326  | 860    |
|  327  | 827    |
|  328  | 827    |

I want a query that returns this

|id    | sub_id  |
|------|---------|
| 827  | 828,829 |
| 828  | null    |
| 829  | null    |
| 860  | 861     |
| 861  | null    |

Thanks a lot!

look at `ListAgg` https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm or `WM_CONCAT` https://asktom.oracle.com/pls/apex/asktom.search?tag=wm-concat-vs-listagg Though WM_CONCAT is undocumented; it's been used for years. Depending on what version of oracle you have... — xQbert, Mar 11 '21 at 15:02
I saw WM_CONCAT somewhere else but my version of Oracle does not recognize it — Jesus Paradinas, Mar 11 '21 at 15:04
Does this answer your question? [SQL Query to concatenate column values from multiple rows in Oracle](https://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle) Looks like there's already an answer to this question. Anyone of those methods should work for you — xQbert, Mar 11 '21 at 15:07
@xQbert - In any case, the question seems to be also about the right way to join the tables (even before needing to aggregate). — , Mar 11 '21 at 15:09
Is the hierarchy always limited to "parents" and "sub-id's" or are you going to have a parent's parent, etc.? Your question, as asked, seems to be limited to parents and direct descendants, and as such it may have certain answers. If it's a more general hierarchy (potentially more levels) then it will have different answers. I ask now, so that we don't learn later that you have a different question than the one you asked. — , Mar 11 '21 at 15:10
Your Sub_ID's are at different dpeths and different fields: 860 SUB_ID is from the ID of the services account whereas the Sub_Id's of the 827 are from the all table... Can you explain that logic better (in terms of business rules) — xQbert, Mar 11 '21 at 15:32
@mathguy That's open to interpretation you have yours, I have mine. I'll let the author indicate what's needed. Which is why the comment is a question. — xQbert, Mar 11 '21 at 15:37
@xQbert - the comment is not only a question; someone has also voted to close the question as a "duplicate". Either that is you, or you influenced someone else with your question. And whether the question is **only** about `listagg` is not a matter of opinion. Clearly the OP needs to join the two tables too, and in a not-entirely-obvious way (as your own answer makes it painfully clear). — , Mar 11 '21 at 15:43
Yep interpretation. Deflect the conversation somewhere else if you need to. I interpreted the author just needed help combining rows to columns. The "Close vote" was me, that's how the site works. It prefers not to have the same question over and over recorded. Which this is very close if not doing, in my interpretation. Thus a vote to close. Others interpret and either agree/disagree. Author has ability to comment to the vote and dissuade others if the "proposed duplicate" doesn't meet their needs. Yet if it does; author can comment as such and others can continue to vote to close. — xQbert, Mar 11 '21 at 16:40
My problem was to join to columns into one separated by a separator. I saw other discussions where WM_CONCAT was used but my database does not accept it. I had tried ListAgg as well but I was struggling to use Order by correctly. With your help I managed to do it on my environment, thanks a lot @xQbert — Jesus Paradinas, Mar 12 '21 at 14:39

xQbert · Accepted Answer · 2021-03-11T16:30:06.930

By joining the all table to service and then back to all in an alias you can get the ID and sub id's in a list that then just need to be combined from multiple rows into 1 column per ID. This should get you the "Raw data" that then needs to be aggregrated...

TESTED: Rextester working example

NOTE: Since you have different "LEVELS" of depth for your sub_ID i'm not really sure what you want so 860 and 123 isn't included because it's a completely different field than the source of 827

SELECT A.ID as ID, A2.ID as Sub_ID
FROM ALL A
LEFT JOIN SERVICES S
 on S.Pareint_ID = A.ID
LEFT JOIN All A2
 on A2.Service_ID = S.ID

Now... if we assume that you have a version of oracle which supports ListAgg

SELECT A.ID as ID, ListAgg(A2.ID,',') within group (Order by A2.ID)  as Sub_ID
FROM ALL A
LEFT JOIN SERVICES S
 on S.Parent_ID = A.ID
LEFT JOIN All A2
 on A2.Service_ID = S.ID
GROUP BY A.ID

Giving Us:

+----+-----+---------+
|    | ID  | SUB_ID  |
+----+-----+---------+
|  1 | 827 | 828,829 |  -- These are all.id's...
|  2 | 828 | NULL    |
|  3 | 829 | NULL    |
|  4 | 860 | NULL    |  --> Now why is 123 present as it's a service.id
|  5 | 861 | NULL    |
+----+-----+---------+

**Note all is a reserved word and either needs to be escaped or if your table name really isn't all; adjust accordingly.

LISTAGG Docs

Thanks for your answer, I have edited my example because as you mention there is an error. I will work with this, which seems to be exactly what I need. — Jesus Paradinas, Mar 11 '21 at 15:52
@JesusParadinas So what exactly were you struggling with here? the join the combining of rows to columns or both? — xQbert, Mar 11 '21 at 16:26

Join two tables in Oracle concatenating results by id

1 Answers1