CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Eray Balkanli
Eray Balkanli

Reputation: 7960

How to add a column keeping incremental unique -letter(s)- for each record to a table in sql?

I have a table in sql server 2008 like

Id  Name      Volume
1   Jack        20
2   John        20
3   White       40
... (23 more)
27   Jennifer   12
28   Brown      33
29   Brown      55
... (23 more)
53   Alex       67
53   Smith      15
53   Mary       73

What I want is to add a column to this table, which keeps unique letter(s) for each different id. Example output:

Id  Name      Volume   LetterKey
1   Jack        20        A
2   John        20        B
3   White       40        C
... (23 more)          (...Z)
27   Jennifer   12        AA  
28   Brown      33        AB
29   Brown      55        AC
... (23 more)         (...AZ)
53   Alex       67        BA
53   Smith      15        BB
53   Mary       73        BC
...                   (...BZ)

We do not need 3-digit letters, ZZ would be the last possible one. I could not find a proper way to add such a column to the table. I appreciate if someone helps me with it. Thanks.

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

With only a two character code, you can use row_number() and some character arithmetic:

with t as (
      select t.*, row_number() over (order by id) as seqnum
      from t
     )
select ((case when seqnum > 26
              then char(ascii('A') + seqnum / 26 - 1)
              else ''
         end) + 
        char(ascii('A') + seqnum % 26) 
       )
from t

Upvotes: 4

Related Questions