Home » SQL & PL/SQL » SQL & PL/SQL » Find unique and sorted field value
| Find unique and sorted field value [message #654863] |
Tue, 16 August 2016 04:51  |
 |
amarbose
Messages: 21
Registered: May 2011
|
Junior Member |
|
|
Hi,
A column named input contains the following values in a table.The column Transformed output contains unique value of the column named input and in sorted manner.
Sample output is present in column Transformed Output.
[b]Input[/b] [b]Transformed output[/b]
G25,G25,G25,G25,G28,G28,G29,G25,G25 G25,G28,G29
G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26 G25,G26,G28,G29
How can this be achieve using SQL
|
|
|
|
|
|
|
|
|
|
| Re: Find unique and sorted field value [message #654913 is a reply to message #654872] |
Tue, 16 August 2016 19:25  |
 |
Barbara Boehmer
Messages: 9082
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is a link to a nice option within a thread of various options.
http://www.orafaq.com/forum/mv/msg/201410/653444/#msg_653444
The following adapts the above to your situation.
SCOTT@orcl_12.1.0.2.0> select * from a_table
2 /
INPUT
-------------------------------------------
G25,G25,G25,G25,G28,G28,G29,G25,G25
G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26
2 rows selected.
SCOTT@orcl_12.1.0.2.0> select replace (column_value, ' ', ',') transformed_output
2 from a_table,
3 xmltable (('xs:string (distinct-values (("' || replace (input, ',', '","') || '")))'))
4 /
TRANSFORMED_OUTPUT
--------------------------------------------------------------------------------
G25,G28,G29
G26,G28,G25,G29
2 rows selected.
[Updated on: Tue, 16 August 2016 19:27] Report message to a moderator |
|
|
|
| Re: Find unique and sorted field value [message #654931 is a reply to message #654872] |
Wed, 17 August 2016 02:51 |
 |
sandeep_orafaq
Messages: 88
Registered: September 2014
|
Member |
|
|
One another way to do this without xmltable
with test as
(select 'G25,G25,G25,G25,G28,G28,G29,G25,G25' col,
regexp_count('G25,G25,G25,G25,G28,G28,G29,G25,G25', ',') + 1 cnt
from dual
union all
select 'G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26' col,
regexp_count('G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26', ',') + 1 cnt
from dual)
select col input,
listagg(new_val, ',') within group(order by new_val) transformed_output
from (select distinct col,
to_char(regexp_substr(col || ',',
'([^,]*),|$',
1,
rnk,
null,
1)) new_val
from (select col, rank() over(partition by col order by rownum) rnk
from test,
table(cast(multiset (select level flg
from dual
connect by level <= cnt) as
sys.odcinumberlist))))
group by col;
Result:
INPUT TRANSFORMED_OUTPUT
------------------------------------------- --------------------------------------------------------------------------------
G25,G25,G25,G25,G28,G28,G29,G25,G25 G25,G28,G29
G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26 G25,G26,G28,G29
|
|
|
|
| Re: Find unique and sorted field value [message #654932 is a reply to message #654931] |
Wed, 17 August 2016 03:10 |
 |
Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
If you use RANK you may lose some rows, use ROW_NUMBER instead.
A slightly simpler solution based on one in the link:
SQL> with
2 test as (
3 select 'G25,G25,G25,G25,G28,G28,G29,G25,G25' val
4 from dual
5 union all
6 select 'G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26' val
7 from dual
8 ),
9 data as (
10 select val, row_number() over (order by null) rn
11 from test
12 )
13 select val input, listagg(word,',') within group (order by word) output
14 from ( select distinct val, rn, regexp_substr(val, '[^,]+', 1, column_value) word
15 from data,
16 table(cast(multiset(select level from dual
17 connect by level <= regexp_count(val,',')+1)
18 as sys.odciNumberList)) )
19 group by rn, val
20 /
INPUT OUTPUT
------------------------------------------- --------------------------------------------------
G25,G25,G25,G25,G28,G28,G29,G25,G25 G25,G28,G29
G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26 G25,G26,G28,G29
2 rows selected. |
|
|
|
|
|
| Re: Find unique and sorted field value [message #654942 is a reply to message #654941] |
Wed, 17 August 2016 04:45 |
|
Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
See:
SQL> with test as
2 (select 'G25,G25,G25,G25,G28,G28,G29,G25,G25' col,
3 regexp_count('G25,G25,G25,G25,G28,G28,G29,G25,G25', ',') + 1 cnt
4 from dual
5 union all
6 select 'G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26' col,
7 regexp_count('G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26', ',') + 1 cnt
8 from dual
9 union all
10 select 'G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26' col,
11 regexp_count('G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26', ',') + 1 cnt
12 from dual)
13 select col input,
14 listagg(new_val, ',') within group(order by new_val) transformed_output
15 from (select distinct col,
16 to_char(regexp_substr(col || ',',
17 '([^,]*),|$',
18 1,
19 rnk,
20 null,
21 1)) new_val
22 from (select col, rank() over(partition by col order by rownum) rnk
23 from test,
24 table(cast(multiset (select level flg
25 from dual
26 connect by level <= cnt) as
27 sys.odcinumberlist))))
28 group by col;
INPUT TRANSFORMED_OUTPUT
------------------------------------------- --------------------------------------------------
G25,G25,G25,G25,G28,G28,G29,G25,G25 G25,G28,G29
G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26 G25,G26,G28,G29
2 rows selected.
|
|
|
|
|
|
| Re: Find unique and sorted field value [message #654946 is a reply to message #654945] |
Wed, 17 August 2016 06:28 |
|
Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
The ORDER BY is ROW_NUMBER/RANK is irrelevant here as you don't care of the order of the rows when you number them, they just need to have a distinct number whatever this later one is.
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 06:44:05 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
