Home » SQL & PL/SQL » SQL & PL/SQL » unique result (Oracle 11g)
unique result [message #660556] Tue, 21 February 2017 03:13 Go to next message
rahul1982
Messages: 45
Registered: November 2011
Location: Pune
Member
Hi,

I have a table where comma separated values are present. My requirement is select only unique values from column 1 and column 2.

I have prepared SQL to select unique records though it has a limitation. If there are more than 10 comma separated values presents that SQL will not work as expected.

How to get unique values irrespective of any number of comma separated values.

DDL -

 CREATE TABLE MAPPING_UPDATE
   (	FOLDER_NAME VARCHAR2(100 BYTE), 
	MAPPING_NAME VARCHAR2(100 BYTE), 
	COLUMN_UPDATE VARCHAR2(1000 BYTE)
   )

DML -

insert into mapping_update (folder_name,mapping_name)
values('zip_map,state_map,city_map','city_map,state_map,conn_map');

insert into mapping_update (folder_name,mapping_name)
values('col_map,name_map,fname_map','fname_map,mname_map,conn_map')

insert into mapping_update (folder_name,mapping_name)
values('dom_map,state_map,city_map','dom_map,date_map,conn_map')


SQL -

with data as(
select substr(folder_name||','||mapping_name,1,
instr(folder_name||','||mapping_name,',',1)-1) as city,folder_name,mapping_name ,rownum srno from mapping_update
union 
select substr(folder_name||','||mapping_name,
decode(instr(folder_name||','||mapping_name,',',1,1),0,0,instr(folder_name||','||mapping_name,',',1,1)+1),
decode(instr(folder_name||','||mapping_name,',',1,2),0,instr(folder_name||','||mapping_name,',',1,1),0,30,
instr(folder_name||','||mapping_name,',',1,2)-instr(folder_name||','||mapping_name,',',1,1)-1)),folder_name,mapping_name,rownum from mapping_update
union 
select substr(folder_name||','||mapping_name,
decode(instr(folder_name||','||mapping_name,',',1,2),0,0,instr(folder_name||','||mapping_name,',',1,2)+1),
decode(instr(folder_name||','||mapping_name,',',1,3),0,instr(folder_name||','||mapping_name,',',1,2),0,30,
instr(folder_name||','||mapping_name,',',1,3)-instr(folder_name||','||mapping_name,',',1,2)-1)),folder_name,mapping_name,rownum from mapping_update
union 
select substr(folder_name||','||mapping_name,
decode(instr(folder_name||','||mapping_name,',',1,3),0,0,instr(folder_name||','||mapping_name,',',1,3)+1),
decode(instr(folder_name||','||mapping_name,',',1,4),0,instr(folder_name||','||mapping_name,',',1,3),0,30,
instr(folder_name||','||mapping_name,',',1,4)-instr(folder_name||','||mapping_name,',',1,3)-1)),folder_name,mapping_name,rownum from mapping_update
union 
select substr(folder_name||','||mapping_name,
decode(instr(folder_name||','||mapping_name,',',1,4),0,0,instr(folder_name||','||mapping_name,',',1,4)+1),
decode(instr(folder_name||','||mapping_name,',',1,5),0,instr(folder_name||','||mapping_name,',',1,4),0,30,
instr(folder_name||','||mapping_name,',',1,5)-instr(folder_name||','||mapping_name,',',1,4)-1)),folder_name,mapping_name,rownum from mapping_update
union 
select substr(folder_name||','||mapping_name,
decode(instr(folder_name||','||mapping_name,',',1,5),0,0,instr(folder_name||','||mapping_name,',',1,5)+1),
decode(instr(folder_name||','||mapping_name,',',1,6),0,instr(folder_name||','||mapping_name,',',1,5),0,30,
instr(folder_name||','||mapping_name,',',1,6)-instr(folder_name||','||mapping_name,',',1,5)-1)),folder_name,mapping_name,rownum from mapping_update
union 
select substr(folder_name||','||mapping_name,
decode(instr(folder_name||','||mapping_name,',',1,6),0,0,instr(folder_name||','||mapping_name,',',1,6)+1),
decode(instr(folder_name||','||mapping_name,',',1,7),0,instr(folder_name||','||mapping_name,',',1,6),0,30,
instr(folder_name||','||mapping_name,',',1,7)-instr(folder_name||','||mapping_name,',',1,6)-1)),folder_name,mapping_name,rownum from mapping_update
union 
select substr(folder_name||','||mapping_name,
decode(instr(folder_name||','||mapping_name,',',1,7),0,0,instr(folder_name||','||mapping_name,',',1,7)+1),
decode(instr(folder_name||','||mapping_name,',',1,8),0,instr(folder_name||','||mapping_name,',',1,7),0,30,
instr(folder_name||','||mapping_name,',',1,8)-instr(folder_name||','||mapping_name,',',1,7)-1)),folder_name,mapping_name,rownum from mapping_update
)select folder_name,srno,listagg(city,',') within group (order by city) from data where city is not null 
group by folder_name ,srno
Re: unique result [message #660561 is a reply to message #660556] Tue, 21 February 2017 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> with
  2    data as (select rownum id, folder_name, mapping_name from mapping_update),
  3    split as (
  4      select id, folder_name value from data
  5      union all
  6      select id, mapping_name from data
  7    ),
  8    expand as (
  9      select distinct id, regexp_substr(value, '[^,]+', 1, column_value) value
 10      from split,
 11           table(cast(multiset(select level from dual
 12                               connect by level <= regexp_count(value,',')+1)
 13                 as sys.odciNumberList))
 14    )
 15  select listagg(value,',') within group (order by value) result
 16  from expand
 17  group by id
 18  order by id
 19  /
RESULT
------------------------------------------------------------------------------------
city_map,conn_map,state_map,zip_map
col_map,conn_map,fname_map,mname_map,name_map
city_map,conn_map,date_map,dom_map,state_map
Re: unique result [message #660562 is a reply to message #660561] Tue, 21 February 2017 03:39 Go to previous message
rahul1982
Messages: 45
Registered: November 2011
Location: Pune
Member
thank you so much.
Previous Topic: Comma separate value with column value as column name
Next Topic: Loading techniques
Goto Forum:
  


Current Time: Thu Jan 18 09:01:06 CST 2018

Total time taken to generate the page: 0.01180 seconds