Home » SQL & PL/SQL » SQL & PL/SQL » unique result (Oracle 11g)
unique result [message #660556] |
Tue, 21 February 2017 03:13 |
|
rahul1982
Messages: 53 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 |
|
Michel Cadot
Messages: 68665 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
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 09:40:13 CDT 2024
|