Soham Shah
Soham Shah

Reputation: 571

Concatenate columns with distinct values in oracle

Gurus,

My table structre look like follwoing:

TankListUS    TankListCanada     TankListIndia
----------    --------------     -------------
T111||T222    T444||T222         T555

Now when I run the query:

select (TL.TankListUS || '||'  || TL.TankListCanada || '||'  || TL.TankListIndia)as "OverallSummary" from TankListTL

I get output as

T111||T222||T444||T222||T555

But I don't need duplicate of tanks. I need my output as:

T111||T222||T444||T555

Is this possible?

Upvotes: 0

Views: 2491

Answers (3)

Soham Shah
Soham Shah

Reputation: 571

with t1 as (select 'T111||T222||T444||T222||T555' col from dual),   

t2 as (SELECT  rownum,REGEXP_SUBSTR (col, '[^||]+', 1, RN) SPLIT

from t1 
cross join

(SELECT ROWNUM RN

FROM (SELECT (length(REGEXP_REPLACE (col, '[^||]+'))/2)+1 MAX_VALUE

from t1)

connect by level <= max_value)),

 t3 as (select distinct split as dis_col from t2)

 select

   rtrim (xmlagg (xmlelement (e, dis_col || '||')).extract ('//text()'), '||') d_col

from

   t3;

Upvotes: 0

the_slk
the_slk

Reputation: 2182

Source:

TANKLISTUS  TANKLISTCANADA  TANKLISTINDIA
T111||T222  T444||T222      T555
T111||T222  T444||T111      T555
T111||T666  T444||T222      T555

Code:

WITH src AS
(
        SELECT 'T111||T222' TankListUS, 'T444||T222' TankListCanada, 'T555' TankListIndia FROM DUAL UNION ALL
        SELECT 'T111||T222' TankListUS, 'T444||T111' TankListCanada, 'T555' TankListIndia FROM DUAL UNION ALL
        SELECT 'T111||T666' TankListUS, 'T444||T222' TankListCanada, 'T555' TankListIndia FROM DUAL
)
, step1 AS
(
        SELECT  SUBSTR(TankListUS,     1, 4) AS us1
        ,       SUBSTR(TankListUS,     7)    AS us2
        ,       SUBSTR(TankListCanada, 1, 4) AS ca1
        ,       SUBSTR(TankListCanada, 7)    AS ca2
        ,       TankListIndia                AS in1
        ,       ROWNUM AS r_id
        FROM    src
)
, step2 AS
(
        SELECT us1 AS r_value, r_id FROM step1 UNION
        SELECT us2, r_id FROM step1 UNION
        SELECT ca1, r_id FROM step1 UNION
        SELECT ca2, r_id FROM step1 UNION
        SELECT in1, r_id FROM step1
)
,step3 AS
(
        SELECT  r_value
        ,       LEAD(r_value, 1) OVER (PARTITION BY r_id ORDER BY r_value) AS lead1
        ,       LEAD(r_value, 2) OVER (PARTITION BY r_id ORDER BY r_value) AS lead2
        ,       LEAD(r_value, 3) OVER (PARTITION BY r_id ORDER BY r_value) AS lead3
        ,       LEAD(r_value, 4) OVER (PARTITION BY r_id ORDER BY r_value) AS lead4
        ,       ROW_NUMBER()     OVER (PARTITION BY r_id ORDER BY r_value) AS r_num
        FROM    step2
)
,step4 AS
(
        SELECT  r_value
        ||      NVL2(lead1, '||' ||  lead1, lead1)
        ||      NVL2(lead2, '||' ||  lead2, lead2)
        ||      NVL2(lead3, '||' ||  lead3, lead3)
        ||      NVL2(lead4, '||' ||  lead4, lead4) AS the_result
        FROM    step3
        WHERE   r_num = 1
)
-- OR:
SELECT  DISTINCT the_result AS the_result_with_DISTINCT
FROM    step4

Result:

T111||T222||T444||T555||T666
T111||T222||T444||T555

Upvotes: 1

Max
Max

Reputation: 4077

I have not tested this but try the following :-

with test1 as
(select (TL.TankListUS || '||'  || TL.TankListCanada || '||'  || TL.TankListIndia) as str from TankListTL),
test2 as
(select regexp_substr(str,'[^|]+',1,rownum) split
from test1
connect by level <= length (regexp_replace (str, '[^|]+'))  + 1)
select listagg(split,'||') within group(order by split)
from test2

Do note that listagg was introduced in Oracle 11gR2. Following are some of the string aggregation techniques :-

http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php#listagg

Upvotes: 2

Related Questions