SQL to remove duplicate values within row of data

 

 

 

 

create table t_dup(id number,

col1 varchar2(10),

col2 varchar2(10),

col3 varchar2(10))

 

insert into t_dup values(1,'A','B','C');

insert into t_dup values(2,'A','B','A');

insert into t_dup values(3,'A','A','A');

COMMIT;

 

select id,col1 c from t_dup union all

select id,col2 from t_dup union all

select id,col3 from t_dup;

 

 

select id,col1 c from t_dup union

select id,col2 from t_dup union

select id,col3 from t_dup;

 

select id,listagg(c,',') within group(order by c) output

from(

select id,col1 c from t_dup union

select id,col2 from t_dup union

select id,col3 from t_dup)

group by id;

----------------------------------------------------------------------------------------------------

select * from t_dup,

lateral(select col1 from dual);

 

select * from t_dup,

lateral(select col1 from dual union

select col2 from dual union

select col3 from dual);

 

select * from t_dup,

lateral(select col1 from dual union

select col2 from dual union

select col3 from dual);

 

select * from t_dup,

lateral(select listagg(c,',') within group(order by c) output from (select col1 c from dual union

select col2 from dual union

select col3 from dual));

 

select * from t_dup,

lateral(select listagg(c,',') within group(order by c) output from (select col1 c from dual union

select col2 from dual union

select col3 from dual));

---------------------------------------------------------------------------------------------------------------------

 

select * from (select * from t_dup)

unpivot(v for col in (col1,col2,col3));

 

select id,listagg(distinct v,',') within group(order by id) from (select * from t_dup)

unpivot(v for col in (col1,col2,col3))

group by id;