Use String Row As column

Question

I have UserExams tables shown below I need to display like FirstName, First, Second, Final

I've tried using PIVOT but the aggregation function don't return all StudentMarks

select *
from
(
SELECT     Users.FirstName,
           ExamsNames.Name,
           UserExams.StudentMark
FROM         Exams INNER JOIN ExamsNames ON Exams.ExamNameID = ExamsNames.ExamsNamesID 
                   INNER JOIN UserExams ON Exams.ExamID = UserExams.ExamID 
                   INNER JOIN Users ON UserExams.UserID = Users.UserID 
)t
PIVOT
(
min(StudentMark)
for  Name in ([First],[Second],[Final])
)p

data from userexams table

Taryn · Accepted Answer · 2013-10-23 15:13:08Z

1

The way your current query is written you are going to return one min(StudentMark) for each exam in Name. If you want to return multiple values for each exam, then you will want to include another column that will give you distinct rows - I would suggest using row_number:

select FirstName, [First],[Second],[Final]
from
(
  SELECT Users.FirstName,
    ExamsNames.Name,
    UserExams.StudentMark,
    row_number() over(partition by Users.FirstName, ExamsNames.Name
                      order by UserExams.StudentMark) seq
  FROM Exams 
  INNER JOIN ExamsNames ON Exams.ExamNameID = ExamsNames.ExamsNamesID 
  INNER JOIN UserExams ON Exams.ExamID = UserExams.ExamID 
  INNER JOIN Users ON UserExams.UserID = Users.UserID 
)t
PIVOT
(
  min(StudentMark)
  for  Name in ([First],[Second],[Final])
)p

answered Oct 23, 2013 at 15:13

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

Collectives™ on Stack Overflow

Use String Row As column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related