How to rotate a table horizontally in sql server

Question

i have table with columns as :

Sr.no  Subject  No of class attended    
-------------------------------------
1       English           3
2       Maths             4
3       SocialScience     5

I want the table in this format

English    Maths   SocialScience
---------------------------------
3            4          5

I tried this:

Select case when subject ='Maths' then COUNT(No_of_Candidates) else null  end as Maths

but with this i get the data like this :

 English    Maths   SocialScience
---------------------------------
   3            
               4
                         5

Please help me how should i resolve this..

Answer 1

Using PIVOT

SELECT *
FROM yourtable
PIVOT 
(Sum([No of class attended]) for Subject in ([English],[Maths],[SocialScience])) p