Click here to Skip to main content
15,893,161 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
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

SQL
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.
Posted

1 solution

Try this

select StudentName ,

Min(case Gender when 'Male' then Mark end)as Male,
Max( case Gender when 'Female'then Mark end )as Female
from Student
group by StudentName

Please check the table name and column name.
 
Share this answer
 
Comments
deepak thomas 8-Dec-10 23:47pm    
thanks very much
Anju Gowri 8-Dec-10 23:54pm    
Welcome..

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900