0

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!

2
Contributors
3
Replies
18
Views
4 Years
Discussion Span
Last Post by Reverend Jim
0

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.

0

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

0

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.

Edited by Reverend Jim

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.