CODEWITHSUNDEEP
sql-server
abodvdv
abodvdv

Reputation: 99

Use String Row As column

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

Upvotes: 0

Views: 55

Answers (1)

Taryn
Taryn

Reputation: 247670

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

Upvotes: 1

Related Questions