SQL Magic Needed

[penfold_99]

Hi All,

I am in need of some sql magic,
i have a table with the follow columns

teacher , subject ,english, maths, science
mr smith, science ,27 , 28 , 30

i would like to return the following
mr smith, science,30

is this possible with sql in access?

i would prefer doing this in sql and not vba.

[jamesb]

SELECT teacher, subject, CASE subject
  WHEN 'science' THEN science
  WHEN 'maths' THEN maths
  WHEN 'english' THEN english
  END
FROM table

That might do it. I assumed you didn't want a simple 'SELECT teacher, subject, science FROM table' query.

[penfold_99]

thanks but there will be upto 20 subjects and i would like only one query.

[localzuk]

You need to normalise your database, storing the reference to a column within the same table as that column is not good practice from what I know.

Would it not be better to store the column with science/maths etc in it with an id linking it to the original table not be better? Then you can do a simple nested SELECT?

[jamesb]

Well, doing it that way would give you a list of teacher, subject, subject_mark for each teacher quite happily, but it would involve typing out each subject in the CASE statement. Can't find anything to simply pull the CASE statement out of the table itself but I'll keep looking.

(Note: Started typing this before reading localzuk's reply, so it has no bearing on that)

[penfold_99]

hi thanks for your ideas.

it wont be possible to normalise the database as im pulling most of the data from anthor database which i dont have control and trying to make the best of a crap database.

i thought about a case statement but would be hard to keep upto date.

I think i will have to do it via vba.

[NotSoFast]

I'll start by saying I don't know the answer (!)

However, I wonder if it's possible to get the list of current subjects in the database using a SELECT DISTINCT query, then loop through the results of that running a second query for each subject returned. If so, that wouldn't need updating if the subjects changed.

I've never ever used them, but would stored procedures be able to handle this?

Article here that has the basics on stored procedures.

Just a thought.

[monkeyx]

I have been putting of sorting out something like this in teh Serco Facility DB.

Anyhow this great article gives some pointers to how you might do something like this

Arrays and Lists in SQL Server 2005

[MattMitchell]

Is your source table stored in Access or MS-SQL? Probably the best way is to have a bit of VB code that will read in the un-normalised data and just populate a more normalised table (i.e. with the output in the form you're after) in code.