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, Nov 22 '16 at 16:17
Replacing the commas still returns 4 rows though, I'd like it down to 1 row if possible — J.Cart, Nov 22 '16 at 16:28
ahh then you need `wm_concat()` or `ListAgg()` depending on the version of oracle you're using. — xQbert, Nov 22 '16 at 16:28

Thorsten Kettner · Answer 1 · 2016-11-22T16:53:09.240

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 '16 at 16:53

answered Nov 22 '16 at 16:27

Thorsten Kettner

89,309
7
49
73

Depending on version of Oracle, LISTAGG (11g) may not be available. Consider use of undocumented feature WM_CONCAT() instead if LISTAGG not supported. – xQbert Nov 22 '16 at 16:31
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 – J.Cart Nov 22 '16 at 16:33
Strange. Maybe using the same name as an alias causes the problem. Can you try replacing `as comments` with `as comment_list`, please? – Thorsten Kettner Nov 22 '16 at 16:34
or possibly alias the subquery "B" and put B. in listagg and comments. (before comments word) – xQbert Nov 22 '16 at 16:35
Still the same error, it's identifying the 'order by comments' as the problem, though comments is definitely a valid column. It does the same thing if I try to order it by servno too – J.Cart Nov 22 '16 at 16:36
1

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 – J.Cart Nov 22 '16 at 16:40
`to_char(comments)`? Where does that come from? It's not in my query. – Thorsten Kettner Nov 22 '16 at 16:41
Thanks everyone, xQbert and Thorsten Kettner! – J.Cart Nov 22 '16 at 16:42
comments is NCLOB, so to_char was to make it a string – J.Cart Nov 22 '16 at 16:42
Okay, right, then the result of `to_char(comments)` needs a name. I simply didn't expect `comments` not to be varchar2 :-) – Thorsten Kettner Nov 22 '16 at 16:44

score 0 · Answer 2 · answered Nov 22 '16 at 16:39

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

score 0 · Answer 3 · answered Nov 22 '16 at 16:40

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;

Oracle - Multiple Rows from Multiple Tables Into One Field

3 Answers3