How to display columns as rows in a SQL query?

Question

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?

search for CROSS TAB, PIVOT and DYNAMIC SQL

Squirrel
– Squirrel

2016-11-15 09:35:44 +00:00
Commented Nov 15, 2016 at 9:35 — Squirrel
– Squirrel, Commented Nov 15, 2016 at 9:35
Use dynamic pivot concept

Mansoor
– Mansoor

2016-11-15 09:42:40 +00:00
Commented Nov 15, 2016 at 9:42 — Mansoor
– Mansoor, Commented Nov 15, 2016 at 9:42

Chanukya · Accepted Answer · 2016-11-15 09:50:46Z

2

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

edited Nov 15, 2016 at 9:50

answered Nov 15, 2016 at 9:42

Chanukya

5,8911 gold badge24 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mohamed Nor · Accepted Answer · 2016-11-16 08:23:18Z

Finally I've used next part of @Chanukya Answer with little Changes in that Answer i was getting error at line 5 becouse of parentheses in FROM (yourquery) c

Declare @query nvarchar(max);

DECLARE @cols AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.subject) 
            FROM tblExamResults c   ' parentheses Removed from (tblExamResults) c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');
       print(@cols)
     set @query = '
SELECT *
FROM (
   SELECT     tblStudents.Full_Name, tblExamResults.Subject, tblExamResults.Marks
FROM         tblExamResults INNER JOIN
                      tblStudents ON tblExamResults.Student_id = tblStudents.Student_ID
) as s
PIVOT
(
    sum(marks) FOR subject IN ('+ @cols +')
)AS pvt'
;
execute(@query)

Collectives™ on Stack Overflow

How to display columns as rows in a SQL query?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related