Using self Joins to expand database table

Question

I have a table in a database with two three columns ID, NAME, SUBJECT, SCORE There are only three possible values for subject math, physics,biology and for each there is a corresponding score in the score field. Because each student can take more than one course, the NAME column is not unique.

I want to expand the table such that each of the values for subject become a column so i no longer have the subject and score column, In other words I will have NAME, MATH,PHYSICS,BIOLOGY where the values of math physics and biology are the scores and the NAME column then becomes unique

I presently do this by using the query

SELECT T1.NAME, T2.SCORE AS BIOLOGY, T3.SCORE AS PHYSICS, T4.SCORE AS MATH
FROM studenttable T1
LEFT JOIN studenttable T2
ON T1.ID = T2.ID AND T2.SUBJECT = 'biology'
LEFT JOIN studenttable T3
ON T1.ID = T3.ID AND T3.SUBJECT = 'physics'
LEFT JOIN studenttable T4
ON T1.ID = T4.ID AND T4.SUBJECT = 'math'

This seems to work but I was wondering if there is a better way to achieve this? or a more tidier way ? Please do tell

score 0 · Answer 1 · answered Sep 16 '13 at 22:53

Assuming id uniquely identifies a student, id, subject is unique on the table and every id has the same name:

Select
    id,
    name,
    min(case when subject='biology' then score end) as biology,
    min(case when subject='physics' then score end) as physics,
    min(case when subject='math' then score end) as math,
From
    studenttable
Group By
    id,
    name

If your database supports pivot it may be simpler to use that.

score 0 · Answer 2 · answered Sep 16 '13 at 22:55

0

I don't know if it would be considered a better way, but pivot might be a bit cleaner.

select *
from (
  select name, subject, score
  from studenttable
) pivot (
  max(score),
  for subject in ('biology', 'physics', 'math')
)

answered Sep 16 '13 at 22:55

Brian

451
4
15

Using self Joins to expand database table

2 Answers2