How to rotate a table horizontally in sql server

As you said you don't want the output like this:

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

You need to use Subquery like this:

SELECT English,Maths,SocialScienceFROM (SELECT Subject,No_of_class_attended  FROM mytable) upPIVOT (Sum([No_of_class_attended])  for Subject in ([English],[Maths],[SocialScience])) p

Output:

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

See this SQLFiddle

For more see SQL SERVER – PIVOT and UNPIVOT Table Examples

Using PIVOT

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

podiluska solution is correct, I just would like to share a dynamic solution if you would like to add other subjects into the table, and do not want to change the query. However it has some limitation around the length, but you can use it for certain situations for sure:

DECLARE @SQL nvarchar(MAX)DECLARE @ColNames nvarchar(max)SET @ColNames = ''SELECT @ColNames = (CASE WHEN subjects.Subject IS NOT NULL THEN @ColNames + '[' + subjects.Subject + '],' ELSE '' END)FROM subjectsSET @ColNames = LEFT(@ColNames, LEN(@ColNames) - 1)EXEC('SELECT *FROM subjectsPIVOT (Sum([classNum]) for Subject in (' + @ColNames + ')) p')

Here is an SQL Fiddle.