Oracle - Multiple Rows from Multiple Tables Into One Field

Question

I'm looking for a query that can take multiple fields from multiple tables and merge them into one field. Ex. I have a query that looks like:

select to_char(a.comments), to_char(b.comments), to_char(c.comments) from crm.custprob a left join crm.custcall b on b.servno = a.servno left join crm.custlog c on c.servno = b.servno where a.servno = 1716942

and produces the output:

 a.comments b.comments c.comments 1 Regular 3 Primary 5 Log 1 Regular 3 Primary 4 Log 1 Regular 2 Other 5 Log 1 Regular 2 Other 4 Log

What I would like is a query that would produce the output all in one line, and in one field (I don't care about the order of the numbers) so it would look like:

 Comments 1 Regular 2 Other 3 Primary 4 Log 5 Log

use the || opoerator (replace comma's with || in your select) or nest concat's since the function only supports 2 strings, you'd have to nest a concat. — xQbert
– xQbert, Commented Nov 22, 2016 at 16:17
Replacing the commas still returns 4 rows though, I'd like it down to 1 row if possible — J.Cart
– J.Cart, Commented Nov 22, 2016 at 16:28
ahh then you need wm_concat() or ListAgg() depending on the version of oracle you're using. — xQbert
– xQbert, Commented Nov 22, 2016 at 16:28

Thorsten Kettner · Accepted Answer · 2016-11-22 16:53:09Z

1

First get a list of all comments. You'd use UNION for this, not joins. Then use LISTAGG to aggregate rows and concatenate the comments:

select listagg(comments, ' ') within group (order by comments) as comments from ( select to_char(comments) as comments from crm.custprob where servno = 1716942 union select to_char(comments) as comments from crm.custcall where servno = 1716942 union select to_char(comments) as comments from crm.custlog where servno = 1716942 );

(And in case there can be no duplicates, you'd use UNION ALL instead of UNION).

(Updated according to below comments where it shows that comments is of type NCLOB.)

edited Nov 22, 2016 at 16:53

answered Nov 22, 2016 at 16:27

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

xQbert Over a year ago

Depending on version of Oracle, LISTAGG (11g) may not be available. Consider use of undocumented feature WM_CONCAT() instead if LISTAGG not supported.

J.Cart Over a year ago

This looks close, but it's throwing the error 'ORA000904: "COMMENTS": invalid identifier" referring to the comments in the "within group (order by comments)". I tried replacing it with 'crm.custprob.comments' mbut it still errors out

Thorsten Kettner Over a year ago

Strange. Maybe using the same name as an alias causes the problem. Can you try replacing as comments with as comment_list, please?

xQbert Over a year ago

or possibly alias the subquery "B" and put B. in listagg and comments. (before comments word)

J.Cart Over a year ago

Here was the problem, the lines in the union needed to have aliases too, ex: select to_char(comments) as comments from crm.custprob where servno = 1716942 union

|

Boneist · Accepted Answer · 2016-11-22 16:39:56Z

Here's an alternative that does the listagg on the individual tables before joining:

WITH t1 AS (SELECT 1 ID, '1 regular' comments FROM dual UNION ALL SELECT 2 ID, '1 abnormal' comments FROM dual), t2 AS (SELECT 1 ID, '2 other' comments FROM dual UNION ALL SELECT 1 ID, '3 primary' comments FROM dual UNION ALL SELECT 2 ID, '2 something else' comments FROM dual UNION ALL SELECT 2 ID, '3 secondary' comments FROM dual), t3 AS (SELECT 1 ID, '4 log' comments FROM dual UNION ALL SELECT 1 ID, '5 log' comments FROM dual UNION ALL SELECT 2 ID, '4 log' comments FROM dual UNION ALL SELECT 2 ID, '5 log' comments FROM dual) SELECT t1.id, t1.comments||' '||t_2.comments||' '||t_3.comments comments FROM t1 LEFT OUTER JOIN (SELECT ID, listagg(comments, ' ') WITHIN GROUP (ORDER BY comments) comments FROM t2 GROUP BY ID) t_2 ON t1.id = t_2.id LEFT OUTER JOIN (SELECT ID, listagg(comments, ' ') WITHIN GROUP (ORDER BY comments) comments FROM t3 GROUP BY ID) t_3 ON t_2.id = t_3.id; ID COMMENTS ---------- -------------------------------------------------------------------------------- 1 1 regular 2 other 3 primary 4 log 5 log 2 1 abnormal 2 something else 3 secondary 4 log 5 log

Gordon Linoff · Accepted Answer · 2016-11-22 16:40:05Z

Does this work?

select listagg(comments, ' ') within group (order by ordering) as comments from (select comments from crm.custprob, 1 as ordering where servno = 1716942 union all select comments from crm.custcall, 2 where servno = 1716942 union all select comments from crm.custlog, 3 where servno = 1716942 ) x;

Collectives™ on Stack Overflow

Oracle - Multiple Rows from Multiple Tables Into One Field

3 Answers 3

10 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

Comments

Comments

Linked

Related