I have got a table
subjectname mark gender
maths 10 male
maths 20 female
physics 30 male
physics 40 female
chemistry 50 male
chemistry 60 female
i need to write an sql query to display the above table as follows
subjectname male female
maths 10 20
physics 30 40
chemistry 50 60 .How can i do this?
I tried the following query
select subjectname,case(gender) when 'male' then mark
else mark end from Test1 group by(subjectname)
but got error
Msg 8120, Level 16, State 1, Line 1
Column 'Test1.gender' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'Test1.mark' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 'Test1.mark' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.