adding a column and values to it on the basis of some existing colum values

i have retrived this datatable from datasource and i want to add a more colum 'category' based on the each value of each Role, like if role column has the value '90 daya client' then in the same row it should have a value 'DC' in the category column. Please let me now how can acieve this. i can make a temporatry dt1 table but to add related values.

 S.No     First Name     Last Name           Role             Date
    1   Us er1Name  User1Name   90 Day Client   11/01/2011
    2   User1NameAtt1   User1NameAtt1   90 Day Client   11/01/2011
    3   User4Name   User4Name   Student Client  11/01/2011
    4   User4NameAtt1   User4NameAtt1   Student Client  11/01/2011
    5   user1   user1   90 Day Client   90 Day Client   11/01/2011
    6   att1    att1    Employee        Accountin   11/01/2011
    7   User4NameAtt2   User4NameAtt2   Student Client  11/01/2011
    8   e   ertrt   90 Day Client   Student Client  11/01/2011
    9   User4NameAtt3   User4NameA开发者_JAVA技巧tt3   Employee    11/01/2011

If using SQL-Server, you could add this column in your sql query with CASE-Expression. For example(if your table is called Students:

SELECT Students.*, Category =
      CASE Role
         WHEN '90 Day Client' THEN 'DC'
         WHEN 'Student Client' THEN 'SC'
         WHEN 'Accountin' THEN 'AC'
         WHEN 'Employee' THEN 'EM'
         ELSE ''
      END
FROM Students