I need to concatenate two columns from diffrent tables, delimited with ";" into one row without duplicates.

Table 1:

Name
John;Sue

Table 2:

Name
Mary;John

Desired output

Names
John;Sue;Mary

I tried with :

select listagg(a.Name, ';') within group (order by a.Name) as Names
from Table1 a
join Table2 b on a.id = b.id;

but I get "ORA-01489: result of string concatenation is too long" error.

How to do that properly in Oracle?

CodePudding user response：

You can do it with simple string functions:

WITH t1_positions (id, name, spos, epos) AS (
  SELECT id,
         name,
         1,
         INSTR(name, ';', 1)
  FROM   table1
UNION ALL
  SELECT id,
         name,
         epos   1,
         INSTR(name, ';', epos   1)
  FROM   t1_positions
  WHERE  epos > 0
),
t1_strings (id, item) AS (
  SELECT id,
         CASE epos
         WHEN 0
         THEN SUBSTR(name, spos)
         ELSE SUBSTR(name, spos, epos - spos)
         END
  FROM   t1_positions
),
t2_positions (id, name, spos, epos) AS (
  SELECT id,
         name,
         1,
         INSTR(name, ';', 1)
  FROM   table2
UNION ALL
  SELECT id,
         name,
         epos   1,
         INSTR(name, ';', epos   1)
  FROM   t2_positions
  WHERE  epos > 0
),
t2_strings (id, item) AS (
  SELECT id,
         CASE epos
         WHEN 0
         THEN SUBSTR(name, spos)
         ELSE SUBSTR(name, spos, epos - spos)
         END
  FROM   t2_positions
)
SELECT id,
       LISTAGG(item, ';') WITHIN GROUP (ORDER BY item) AS name
FROM   (SELECT * FROM t1_strings
        UNION
        SELECT * FROM t2_strings)
GROUP BY id;

Which, for the sample data:

CREATE TABLE Table1 (id, name) AS
SELECT 1, 'John;Sue' FROM DUAL;

CREATE TABLE Table2 (id, name) AS
SELECT 1, 'Mary;John' FROM DUAL;

Outputs:

ID	NAME
1	John;Mary;Sue

Note: you can do it with regular expressions; however, for a large dataset, it is likely to be of an order of magnitude slower.

Update

How to do that properly in Oracle?

Do not store delimited strings and store the data in first normal form (1NF):

CREATE TABLE table1 (id, name) AS
SELECT 1, 'John' FROM DUAL UNION ALL
SELECT 1, 'Sue' FROM DUAL;

CREATE TABLE table2 (id, name) AS
SELECT 1, 'Mary' FROM DUAL UNION ALL
SELECT 1, 'John' FROM DUAL;

Then the query is simply:

SELECT id,
       LISTAGG(name, ';') WITHIN GROUP (ORDER BY name) AS name
FROM   (SELECT * FROM table1
        UNION
        SELECT * FROM table2)
GROUP BY id;

db<>fiddle here

CodePudding user response：

Presuming those are names and the result doesn't span over more than 4000 characters (which is the listagg limit) then one option is to do this (read comments within code):

SQL> with
  2  -- sample data
  3  table1 (id, name) as
  4    (select 1, 'John;Sue'    from dual union all
  5     select 2, 'Little;Foot' from dual),
  6  table2 (id, name) as
  7    (select 1, 'Mary;John' from dual),
  8  --
  9  union_jack (id, name) as
 10    -- union those two tables
 11    (select id, name from table1
 12     union
 13     select id, name from table2
 14    ),
 15  distname as
 16    -- distinct names
 17    (select distinct
 18            id,
 19            regexp_substr(name, '[^;] ', 1, column_value) name
 20     from union_jack cross join
 21          table(cast(multiset(select level from dual
 22                              connect by level <= regexp_count(name, ';')   1
 23                             ) as sys.odcinumberlist))
 24    )
 25  select id,
 26         listagg(d.name, ';') within group (order by d.name) as names
 27  from distname d
 28  group by id;

        ID NAMES
---------- ------------------------------
         1 John;Mary;Sue
         2 Foot;Little

SQL>

If it really spans over more than 4000 characters, switch to XMLAGG; lines #25 onward would be

 25  select id,
 26         rtrim(xmlagg (xmlelement (e, d.name || ';') order by d.name).extract
 27                  ('//text()'), ';') as names
 28  from distname d
 29  group by id;

        ID NAMES
---------- ------------------------------
         1 John;Mary;Sue
         2 Foot;Little

SQL>

CodePudding user response：

You can use a XML-style tecnique before applying LISTAGG() in order to provide writing distinct names such as

WITH t AS
( 
  SELECT RTRIM(DBMS_XMLGEN.CONVERT(
                 XMLAGG( 
                        XMLELEMENT(e,name||';')
                       ).EXTRACT('//text()').GETCLOBVAL() ,1),
                ';') AS name  
      FROM ( SELECT t1.name||';'||t2.name AS name
               FROM table1 t1 JOIN table2 t2 ON t1.id=t2.id )
)
 SELECT LISTAGG(REGEXP_SUBSTR(name,'[^;] ',1,level),';') 
        WITHIN GROUP (ORDER BY 0) AS "Names"
   FROM t
CONNECT BY level <= REGEXP_COUNT(name,';')                               

Demo