Mohamed Nor Mohamed Nor - 25 days ago 6
SQL Question

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

tblstudents

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:

enter image description here

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)

enter image description here

So:


  • How I can display data in that format?

  • Is that possible in SQL Server?


Answer
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