CODEWITHSUNDEEP
sqloracle11g
mano
mano

Reputation: 67

Getting distinct values of induvidual columns

I have a table A as below

id| Name|Subject    
--|-----|-------
1 |Mano |Science   
2 |Pavi |Maths    
3 |Mano |Social    
1 |Kalai|Maths  
4 |Kalai|Science

I want distinct values for each column. So My output be like

id|Name | Subject    
--|-----|--------
1 |Mano |Science    
2 |Pavi |Maths    
3 |Kalai|Social    
4 |     |

I have tried using cursors. But I didn't get what I needed. Anybody help me in getting this

Upvotes: 1

Views: 39

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

select    *

from     (select  col,val,dense_rank () over (partition by col order by val)  as dr    
          from    mytable unpivot (val for col in (name,subject)) u
          ) pivot (min(val) for col in ('NAME','SUBJECT'))    

order by  dr

+----+-------+---------+
| DR | NAME  | SUBJECT |
+----+-------+---------+
|  1 | Kalai | Maths   |
|  2 | Mano  | Science |
|  3 | Pavi  | Social  |
+----+-------+---------+

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You seem to just want a list of the distinct values, without regards to what appears together. This isn't very SQL'ish, but can be done:

select row_number() over (order by n.seqnum) as firstcol, n.name, s.subject
from (select name, row_number() over (order by name) as seqnum
      from t
      group by name
     ) n full outer join
     (select subject, row_number() over (order by subject) as seqnum
      from t
      group by subject
     ) s
     on s.seqnum = n.seqnum;

Upvotes: 1

Related Questions