|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/15/2009 10:24:11 AM
Posts: 1,
Visits: 4
|
|
Hi all, I am trying to create a new variable depending on already existing values in the table but got stuck.. here is the example
colA colB new_col1 new_col2
jet a 1 1
jet b 1 1
cat c 1 2
cat d 1 2
dog e 2 3
hen f 3 4
I need to create new columns (new_col1 & new_col2) . I am looking to assign a common value for new_col1 if the values in colA are duplicates and values in colB are unique (i.e jet and cat should get the value of 1 in new_col1) and assign a separate value for each duplicate value in colA (i.e . jet should get 1 and cat should get 2 in new_col2).
Could anyone please help me in getting this result? Really appreciate any help on this issue!
Thanks in advance!
Srinivas.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/28/2009 2:09:39 PM
Posts: 1,
Visits: 9
|
|
let's handle these separately to keep it simple.
lets also assume your table is called table one and properly sorted
[font=Courier New]
create table two as
select ColA , row_number as New_ColB
from ( select distinct ColA from one );
** lets count the number of rows per ColA ** ;
create table three as
select ColA , count(*) as CntA
from table one
group by ColA;
create table four as
select ColA ,
case CntA
when 1 then "0"
else "1"
end as New_ColA
from table three ;
** then bring them all together** ;
create table five as
select One.ColA , Four.New_ColA , Two.New_ColB
from one left join two
on One.ColA = Four.ColA
left join two
on One.ColA = Two.ColA ;
[/font]
|
|
|
|