 3,733,252 Users
 2,246,738 Discussions
 7,856,634 Comments
Forum Stats
Discussions
Howdy, Stranger!
Categories
 380.9K All Categories
 2.1K Data
 203 Big Data Appliance
 1.9K Data Science
 446.1K Databases
 220.4K General Database Discussions
 3.7K Java and JavaScript in the Database
 22 Multilingual Engine
 506 MySQL Community Space
 459 NoSQL Database
 7.7K Oracle Database Express Edition (XE)
 2.8K ORDS, SODA & JSON in the Database
 437 SQLcl
 3.9K SQL Developer Data Modeler
 185.4K SQL & PL/SQL
 20.7K SQL Developer
 291.2K Development
 6 Developer Projects
 116 Programming Languages
 288K Development Tools
 96 DevOps
 3K QA/Testing
 645.2K Java
 16 Java Learning Subscription
 36.9K Database Connectivity
 148 Java Community Process
 104 Java 25
 22.1K Java APIs
 137.7K Java Development Tools
 165.3K Java EE (Java Enterprise Edition)
 12 Java Essentials
 138 Java 8 Questions
 85.9K Java Programming
 79 Java Puzzle Ball
 65.1K New To Java
 1.7K Training / Learning / Certification
 13.8K Java HotSpot Virtual Machine
 94.2K Java SE
 13.8K Java Security
 195 Java User Groups
 22 JavaScript  Nashorn
 Programs
 177 LiveLabs
 33 Workshops
 10.2K Software
 6.7K Berkeley DB Family
 3.5K JHeadstart
 5.7K Other Languages
 2.3K Chinese
 165 Deutsche Oracle Community
 1.2K Español
 1.9K Japanese
 225 Portuguese
Distinct values in each column
439955
Member Posts: 17
Hi
I have a table with three columns and I would like to display distinct values in each column. Could any one please help me to get the output.
col1 col2 col3
==== ==== ====
a 1 x
a 2 y
a 3 z
a 4 u
b 5 v
b 6 x
b 7 x
b 9 x
b 10 y
b 11 y
b 12 y
b 13 y
b 14 x
b 15 y
b 16 z
b 17 u
b 18 v
b 19 x
b 20 x
c 21 x
c 22 y
c 23 y
c 24 y
c 25 y
c 26 x
c 27 y
c 28 z
Output needed
===========
col1 col2 col3
==== ==== ====
a 1 x
b 2 y
c 3 z
4 u
5 v
6
7
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Thanks
Vasanth
I have a table with three columns and I would like to display distinct values in each column. Could any one please help me to get the output.
col1 col2 col3
==== ==== ====
a 1 x
a 2 y
a 3 z
a 4 u
b 5 v
b 6 x
b 7 x
b 9 x
b 10 y
b 11 y
b 12 y
b 13 y
b 14 x
b 15 y
b 16 z
b 17 u
b 18 v
b 19 x
b 20 x
c 21 x
c 22 y
c 23 y
c 24 y
c 25 y
c 26 x
c 27 y
c 28 z
Output needed
===========
col1 col2 col3
==== ==== ====
a 1 x
b 2 y
c 3 z
4 u
5 v
6
7
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Thanks
Vasanth
Comments

Oops.. Some formatting issue.. The result should be like this
col2 col3 col1
==== ==== ====
1 x a
2 y b
3 z c
4 u
5 v
6
7
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28 
Vasanth! You can paste the format between [pre] and [/pre] tags to preserve the spaces.
Usebreak
SQL*Plus command.
Cheers
Sarma. 
Not quite finished but this should give you a huge start
with distinct_cols as ( select 'a' col1 , 1 col2 , 'x' col3 from dual union all select 'a', 2, 'y' from dual union all select 'a', 3, 'z' from dual union all select 'a', 4, 'u' from dual union all select 'b', 5, 'v' from dual union all select 'b', 6, 'x' from dual union all select 'b', 7, 'x' from dual union all select 'b', 9, 'x' from dual union all select 'b', 10, 'y' from dual union all select 'b', 11, 'y' from dual union all select 'b', 12, 'y' from dual union all select 'b', 13, 'y' from dual union all select 'b', 14, 'x' from dual union all select 'b', 15, 'y' from dual union all select 'b', 16, 'z' from dual union all select 'b', 17, 'u' from dual union all select 'b', 18, 'v' from dual union all select 'b', 19, 'x' from dual union all select 'b', 20, 'x' from dual union all select 'c', 21, 'x' from dual union all select 'c', 22, 'y' from dual union all select 'c',23, 'y' from dual union all select 'c', 24, 'y' from dual union all select 'c', 25, 'y' from dual union all select 'c', 26, 'x' from dual union all select 'c', 27, 'y' from dual union all select 'c', 28, 'z' from dual ) select decode(marker,'1',col1,NULL) col1_distinct, decode(marker,'2',col1,NULL) col2_distinct, decode(marker,'3',col1,NULL) col3_distinct from ( select distinct '1' marker,col1 from distinct_cols union all select distinct '2',to_char(col2) from distinct_cols union all select distinct '3',col3 from distinct_cols )

This is a solution but result is not deterministic, and this is also right because the properties of the row in the output are not completely defined.
Processing ...
Bye Alessandro
with distinct_cols
as
( select
'a' col1 , 1 col2 , 'x' col3 from dual
union all select
'a', 2, 'y' from dual
union all select
'a', 3, 'z' from dual
union all select
'a', 4, 'u' from dual
union all select
'b', 5, 'v' from dual
union all select
'b', 6, 'x' from dual
union all select
'b', 7, 'x' from dual
union all select
'b', 9, 'x' from dual
union all select
'b', 10, 'y' from dual
union all select
'b', 11, 'y' from dual
union all select
'b', 12, 'y' from dual
union all select
'b', 13, 'y' from dual
union all select
'b', 14, 'x' from dual
union all select
'b', 15, 'y' from dual
union all select
'b', 16, 'z' from dual
union all select
'b', 17, 'u' from dual
union all select
'b', 18, 'v' from dual
union all select
'b', 19, 'x' from dual
union all select
'b', 20, 'x' from dual
union all select
'c', 21, 'x' from dual
union all select
'c', 22, 'y' from dual
union all select
'c',23, 'y' from dual
union all select
'c', 24, 'y' from dual
union all select
'c', 25, 'y' from dual
union all select
'c', 26, 'x' from dual
union all select
'c', 27, 'y' from dual
union all select
'c', 28, 'z' from dual
)
select col1,col2,col3
from (
select rownum rn
from distinct_cols
) natural left outer join (
select rownum rn,col1
from (
select distinct col1
from distinct_cols
)
) natural left outer join (
select rownum rn,col2
from (
select distinct col2
from distinct_cols
)
) natural left outer join (
select rownum rn,col3
from (
select distinct col3
from distinct_cols
)
)
Query finished, retrieving results...
COL1 COL2 COL3
  
a 6 z
b 7 x
c 12 y
17 u
25 v
10
11
21
16
2
1
28
13
20
26
4
15
3
18
23
27
24
9
5
14
22
19
27 row(s) retrieved 
Thanks to every one... Sarma,Keith Jamieson and Alessandro .
I hopeI can retrive the result now. Thanks again
Vasanth 
No joins, just analytics:
exec dbms_random.seed(0)
with t as ( select chr(ascii('a') + dbms_random.value(0, 3)) as c1,
trunc(dbms_random.value(1, 20)) as c2,
chr(ascii('u') + dbms_random.value(0, 6)) as c3
from dual connect by level <= 20
),

tt1 as ( select lag( null, 1, c1) over (partition by c1 order by null) as c1,
lag(to_number(null), 1, c2) over (partition by c2 order by null) as c2,
lag( null, 1, c3) over (partition by c3 order by null) as c3
from t
),
tt2 as ( select tt1.*,
row_number() over (order by c1) as rn1,
row_number() over (order by c2) as rn2,
row_number() over (order by c3) as rn3
from tt1
),
tt3 as ( select case when rn1 > rn2
then last_value(c1) over (order by rn1 range between abs(rn1  rn2) preceding
and abs(rn1  rn2) preceding)
else last_value(c1) over (order by rn1 range between abs(rn2  rn1) following
and abs(rn2  rn1) following)
end as c1,
c2,
case when rn3 > rn2
then last_value(c3) over (order by rn3 range between abs(rn3  rn2) preceding
and abs(rn3  rn2) preceding)
else last_value(c3) over (order by rn3 range between abs(rn2  rn3) following
and abs(rn2  rn3) following)
end as c3
from tt2
)
select c1, c2, c3 from tt3
where c1  c2  c3 is not null
order by c1, c2, c3
;
C1 C2 C3
  
a 1 u
b 3 v
c 4 w
5 x
7 y
8 z
9
11
13
15
16
17
19
13 rows selected. 
No joins, just analytics:nice, but the only drawback  you need to know the column, comprising the biggest amount of distinct values beforehand.

but the only drawback  you need to know the column, comprising the biggest amount of distinct values beforehand.No. There is no any difference in which column will be chosen as "based".
For demo purposes I've added extra row number column to show full generality and symmetry of the approach:exec dbms_random.seed(0)
P.S. Thanks to [url http://www.sql.ru/forum/actualthread.aspx?bid=3&tid=482506&hl=over+range#4785373]Vladimir Sitnikov for the demo of the approach.
with t as ( select chr(ascii('a') + dbms_random.value(0, 3)) as c1,
trunc(dbms_random.value(1, 20)) as c2,
chr(ascii('u') + dbms_random.value(0, 6)) as c3
from dual connect by level <= 20
),

tt1 as ( select lag( null, 1, c1) over (partition by c1 order by null) as c1,
lag(to_number(null), 1, c2) over (partition by c2 order by null) as c2,
lag( null, 1, c3) over (partition by c3 order by null) as c3
from t
),
tt2 as ( select tt1.*,
row_number() over (order by c1) as rn1,
row_number() over (order by c2) as rn2,
row_number() over (order by c3) as rn3,
rownum rn
from tt1
),
tt3 as ( select case when rn1 > rn
then last_value(c1) over (order by rn1 range between abs(rn1  rn) preceding
and abs(rn1  rn) preceding)
else last_value(c1) over (order by rn1 range between abs(rn  rn1) following
and abs(rn  rn1) following)
end as c1,
case when rn2 > rn
then last_value(c2) over (order by rn2 range between abs(rn2  rn) preceding
and abs(rn2  rn) preceding)
else last_value(c2) over (order by rn2 range between abs(rn  rn2) following
and abs(rn  rn2) following)
end as c2,
case when rn3 > rn
then last_value(c3) over (order by rn3 range between abs(rn3  rn) preceding
and abs(rn3  rn) preceding)
else last_value(c3) over (order by rn3 range between abs(rn  rn3) following
and abs(rn  rn3) following)
end as c3
from tt2
)
select c1, c2, c3 from tt3
where c1  c2  c3 is not null
order by c1, c2, c3
;
C1 C2 C3
  
a 1 u
b 3 v
c 4 w
5 x
7 y
8 z
9
11
13
15
16
17
19
13 rows selected. 
create table ColTable(Col1,Col2,Col3) as
select 'a', 1,'x' from dual union all
select 'a', 2,'y' from dual union all
select 'a', 3,'z' from dual union all
select 'a', 4,'u' from dual union all
select 'b', 5,'v' from dual union all
select 'b', 6,'x' from dual union all
select 'b', 7,'x' from dual union all
select 'b', 9,'x' from dual union all
select 'b',10,'y' from dual union all
select 'b',11,'y' from dual union all
select 'b',12,'y' from dual union all
select 'b',13,'y' from dual union all
select 'b',14,'x' from dual union all
select 'b',15,'y' from dual union all
select 'b',16,'z' from dual union all
select 'b',17,'u' from dual union all
select 'b',18,'v' from dual union all
select 'b',19,'x' from dual union all
select 'b',20,'x' from dual union all
select 'c',21,'x' from dual union all
select 'c',22,'y' from dual union all
select 'c',23,'y' from dual union all
select 'c',24,'y' from dual union all
select 'c',25,'y' from dual union all
select 'c',26,'x' from dual union all
select 'c',27,'y' from dual union all
select 'c',28,'z' from dual;col COl1 for a8
col COl3 for a8
col NewCol1 for a8
col NewCol3 for a8select Col1,Col2,Col3,
case when Rn <= count(distinct Col1) over()
then max(Col1) over(order by Col1SumFlag
range between RnCol1SumFlag following
and RnCol1SumFlag following) end as NewCol1,
case when Rn <= count(distinct Col3) over()
then max(Col3) over(order by Col3SumFlag
range between RnCol3SumFlag following
and RnCol3SumFlag following) end as NewCol3
from (select Col1,Col2,Col3,Rn,
case Col1FirstFlag when 1 then sum(Col1FirstFlag) over(order by Col2)
else 0 end as Col1SumFlag,
case Col3FirstFlag when 1 then sum(Col3FirstFlag) over(order by Col2)
else 0 end as Col3SumFlag
from (select Col1,Col2,Col3,
Row_Number() over(order by Col2) as Rn,
case Row_Number() over(partition by Col1 order by Col2)
when 1 then 1 else 0 end as Col1FirstFlag,
case Row_Number() over(partition by Col3 order by Col2)
when 1 then 1 else 0 end as Col3FirstFlag
from ColTable))
order by Col2;COL1 COL2 COL3 NEWCOL1 NEWCOL3
I used "Baaiwake" two times on case expression.
    
a 1 x a x
a 2 y b y
a 3 z c z
a 4 u null u
b 5 v null v
b 6 x null null
b 7 x null null
b 9 x null null
b 10 y null null
b 11 y null null
b 12 y null null
b 13 y null null
b 14 x null null
b 15 y null null
b 16 z null null
b 17 u null null
b 18 v null null
b 19 x null null
b 20 x null null
c 21 x null null
c 22 y null null
c 23 y null null
c 24 y null null
c 25 y null null
c 26 x null null
c 27 y null null
c 28 z null null
http://aozoragakuen.sakura.ne.jp/houhou5/houhou52/node6.html 
easy version
create table Tumeru as
select 'A' as Val,10 as SortKey from dual union
select 'A', 20 from dual union
select 'B', 30 from dual union
select 'B', 40 from dual union
select 'B', 50 from dual union
select 'C', 60 from dual union
select 'C', 70 from dual union
select 'D', 80 from dual union
select 'D', 90 from dual union
select 'E',100 from dual union
select 'E',110 from dual union
select 'E',120 from dual union
select 'E',130 from dual union
select 'E',140 from dual union
select 'A',150 from dual union
select 'B',160 from dual union
select 'C',170 from dual union
select 'F',180 from dual union
select 'G',190 from dual;col Val for a8
col NewVal for a8select Val,SortKey,
case when Rn <= count(distinct Val) over()
then max(Val) over(order by SumFlag
range between RnSumFlag following
and RnSumFlag following) end as NewVal
from(select Val,SortKey,Rn,
case FirstFlag when 1 then sum(FirstFlag) over(order by SortKey)
else 0 end as SumFlag
from (select Val,SortKey,
Row_Number() over(order by SortKey) as Rn,
case Row_Number() over(partition by Val order by SortKey)
when 1 then 1 else 0 end as FirstFlag
from Tumeru))
order by SortKey;VAL SORTKEY NEWVAL
  
A 10 A
A 20 B
B 30 C
B 40 D
B 50 E
C 60 F
C 70 G
D 80 null
D 90 null
E 100 null
E 110 null
E 120 null
E 130 null
E 140 null
A 150 null
B 160 null
C 170 null
F 180 null
G 190 null 
agreed, I was wrongbut the only drawback  you need to know thecolumn, comprising the biggest amount of distinct
values beforehand.
No. There is no any difference in which column will
be chosen as "based".
For demo purposes I've added extra row number column
to show full generality and symmetry of the
approach:
This discussion has been closed.