pivot sql

[ses03]

i have four tables

(1) tbl_module:

modNo | modName
_ _ _ _ _ _ _ _
1     | module 1
2     | module 2

(2) tbl_subject

subNo | subName | modNo
_ _ _ _ _ _ _ _ _ _ _ _
1     | a1      | 1
2     | b1      | 1
3     | c1      | 1
4     | a2      | 2
5     | b2      | 2

(3) tbl_student

studNo | studName
_ _ _ _ _ _ _ _ _ 
1      | name1
2      | name2

(4) tbl_grade

gradeNo | studNo | subNo |  grade
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
1       | 1      | 1     | 90
2       | 1      | 2     | 80
3       | 2      | 1     | 83

i want to join the tables like this, with row values as column title:

student | a1 | b1 |
_ _ _ _ _ _ _ _ _ _
name1   | 90 | 80
name2   | 83 | -

please? tell me how? I do not know how to make a query with pivot and join in it. heeeelp!

[Reverend Jim]

As far as I know you can't create that table with a join because a1 & b1 are not columns in any table. They are only values of the column subName in tbl_subject.

[ses03]

Hi Reverend Jim. I know it is possible with a single table using PIVOT. I am just confused with using PIVOT with JOIN.

[Reverend Jim]

Since you are not doing any type of aggregation (sum, avg, etc) I don't know how you will do this but you might have a look at a very similar example here. The query

select studName, subName, grade
  from tbl_Student st join tbl_Grade gr
    on st.studNo = gr.studNo
  join tbl_Subject su
    on gr.subNo = su.subNo

gives you

studName subName grade
name1    a1      90
name1    b1      80
name2    a1      83

which is close but not quite there yet.