Q:

How to display columns as rows in a SQL query?

I'm trying to display exam results form simple database containing two tables tblStudents and tblExamResults.
tblstudents contains student ID and Full_Name columns
In tblexamResults columns are Student_id, Subject and Marks.
as in below Picture

1- Currently I am displaying Student results using this query
SELECT     tblStudents.Full_Name, tblExamResults.Subject, tblExamResults.Marks
FROM         tblExamResults INNER JOIN
                      tblStudents ON tblExamResults.Student_id = tblStudents.Student_ID
order by tblStudents.Full_Name

2 - And results looks like in the following picture:

3 - But what I want is to display each subject as row and get result of each subject below it
So that each student's result is displayed in the same row:
Student_Name sub1_result sub2_Result sub3_Result
Like in the following picture (Excel screenshot)

So:

How I can display data in that format?
Is that possible in SQL Server?

A:

select fullname,[english] english, [history] history, [physics] physics
from 
(
  select fullname,subject,marks
  from (yourquery)
) src
pivot
(
  max(marks)
  for subject in ([english], [history], [physics])
) piv;

or
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.subject) 
            FROM (yourquery) c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT fullname, ' + @cols + ' from 
            (
                select fullname,subject,marks
                from (your query)
           ) x
            pivot 
            (
                 max(marks)
                for subject in (' + @cols + ')
            ) p '

execute(@query)
fullname english history physics
    a   85  70  60
    i   60  100 89
    s   90  90  99

