Concat distinct value in oracle sql

How can I concat distinct value in oracle sql?

My table structure look like following:

create table myTable (user, acc_1, acc_2,acc_3,acc_4) as
 select   1, t1, t1, null,null from dual union all
select   2, t2, null, t2,t2  from dual union all
select   3, null, t3, null,null  from dual ;

when I run the query:

select user, acc_1 || ','||acc_2||','||acc_3||','||acc_4 as acc
from myTable

I get output as:

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

Is this possible?

Easiest way would be "unpivot" your data, and treat it as row for this.
For example:

with unpivot_data as (
select user_, acc_1 acc from myTable where acc_1  is not null
union all
select user_, acc_2 from myTable where acc_2 is not null
union all
select user_, acc_3 from myTable where acc_3 is not null
union all
select user_, acc_4 from myTable where acc_4 is not null
  )
select user_, listagg(distinct acc, ', ') 
  from unpivot_data
 group by user_

Here I converted your data to rows, and used LISTAGG(DISTINCT ...) to join them as described.

Demo here.

Obviously you can also use UNPIVOT here too.

EDIT: as @Littlefoot correctly pointed out suggested solution will not work for older versions of Oracle. For them it is possible to use UNION instead of UNION ALL and skip DISTINCT in aggregation function.

with unpivot_data as (
select user_, acc_1 acc from myTable where acc_1  is not null
union 
select user_, acc_2 from myTable where acc_2 is not null
union 
select user_, acc_3 from myTable where acc_3 is not null
union 
select user_, acc_4 from myTable where acc_4 is not null
  )
select user_, listagg(acc, ', ')  WITHIN GROUP (order by acc)
  from unpivot_data
 group by user_

Given the sample data:

create table myTable ("USER", acc_1, acc_2,acc_3,acc_4) as
select 1, 't1', 't1', null, null from dual union all
select 2, 't2', null, 't2', 't2' from dual union all
select 3, null, 't3', null, null from dual;

You can use CASE expressions (which is more to type but will only require a single table scan and uses simple, fast comparisons):

SELECT "USER",
          CASE WHEN acc_1 IS NOT NULL
          THEN acc_1 || ',' END
       || CASE WHEN acc_2 IS NOT NULL
               AND  (acc_1 IS NULL OR acc_1 <> acc_2)
               THEN acc_2 || ',' END
       || CASE WHEN acc_3 IS NOT NULL
               AND  (acc_1 IS NULL OR acc_1 <> acc_3)
               AND  (acc_2 IS NULL OR acc_2 <> acc_3)
               THEN acc_3 || ',' END
       || CASE WHEN acc_4 IS NOT NULL
               AND  (acc_1 IS NULL OR acc_1 <> acc_4)
               AND  (acc_2 IS NULL OR acc_2 <> acc_4)
               AND  (acc_3 IS NULL OR acc_3 <> acc_4)
               THEN acc_4 || ',' END AS accounts
FROM   myTable;

Which outputs:

Note: if you want to remove the trailing comma then you can use RTRIM (or SUBSTR up to the penultimate character if the account numbers may contain commas).

or, unpivot and the re-aggregate the rows (which is much less to type but it is going to be less efficient to unpivot and then re-aggregate compared to doing it all on the initial rows with CASE expressions):

SELECT "USER",
       LISTAGG(DISTINCT acc) WITHIN GROUP (ORDER BY type) AS accounts
FROM   myTable
UNPIVOT (acc FOR type IN (acc_1, acc_2, acc_3, acc_4))
GROUP BY "USER"

Which outputs:

fiddle

Simply use COALESCE to pick the first non-null acc value for each user:

select user, coalesce(acc_1, acc_2, acc_3, acc_4) as acc
from myTable