By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,949 Members | 1,086 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

How to convert then combine field values

P: 72
I have a StudentsTable which has 3 fields to describe which class a student belongs to.

The first is session, in the db it is stored as 1 or 2.
afternoon,morning (values 1, 2)

Language, also stored as 1, 2.
English, Chinese (values 1, 2)

Class number which has values K1,K2,3,4,G...etc. This list is long and changes from time to time.

For example a student who goes to class morning,english,K2 is known as class MEK2 (values 2,1,K2 in his record in the database)

I want to know how many different classes there are in the school.

This much of the sql I know...

step 1
First convert from numeric values to alpha
Expand|Select|Wrap|Line Numbers
  1. CASE WHEN Session=1 THEN 'E' ELSE 'C' END
  2. CASE WHEN Language=1 THEN 'A' ELSE 'M' END
step 2
Combine the three parts into one alpha value eg MEK2, AC4, etc

step 3
Do steps 1,2 for all of the student records but take only the distinct values. (I can then display this list in a dropdown box.)

Can anyone help me with the sql to extract the information?

I think the sql might be something like this but obviously it does not work.

Expand|Select|Wrap|Line Numbers
  1. select concat(sess,lang,classnum) as clas from studentstbl inner join  where studentstbl.sess, studentstbl.lang in
  2. (select case when session=1 then 'a' else 'm' end as sess, case when language=1 then 'm' else 'c' end as lang from studentstbl)
Jun 3 '15 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.