Rock Rock - 4 years ago 64
SQL Question

Add Value column using another column as Key

Hopefully the table itself states the problem. Essentially with the

Type
column on the left, is it possible to add a unique code/value column using
Type
as a hash key/set based on the appearance orders of the types:

Type | Code
-----------
ADA | 1
ADA | 1
BIM | 2
BIM | 2
CUR | 3
BIM | 2
DEQ | 4
ADA | 1
... | ...


We can't simply hard-code the conversion as each time there's arbitrary number of
Type
s.

Answer Source

You can use dense_rank():

select type, dense_rank() over (order by type) as code
from t;

However, I would advise you to create another table and to use that:

create table Types as (
    select row_number() over (order by type) as TypeId,
           type
    from t
    group by type;

Then, join that in:

select t.type, tt.TypeId
from t join
     types tt
     on t.type = tt.type;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download