Query

[Mary Phelps]

i have a table studentsubject with
StudentId(int)
SubjectId(int)

Table Subject has
SubjectId(int)
SubjectName(varchar(50))

Table studentsubject has data in following format
studentid subjectid
1 2
1 3
1 7
2 6
2 8
2 1
I would like to display data in following format:
StudentId SubjectName SubjectName SubjectName
1 English Physics Biology
2 Maths Economics History

Regards
Mary

[Erland Sommarskog]

Post by Mary Phelps
i have a table studentsubject with
StudentId(int)
SubjectId(int)
Table Subject has
SubjectId(int)
SubjectName(varchar(50))
Table studentsubject has data in following format
studentid subjectid
1 2
1 3
1 7
2 6
2 8
2 1
StudentId SubjectName SubjectName SubjectName
1 English Physics Biology
2 Maths Economics History

WITH numbering AS (
SELECT SS.StudentId, Su.SubjectName,
rowno = row_number() OVER(PARTITION BY SS.StudentId
ORDER BY Su.SubjectId)
FROM StudentSubject SS
JOIN Subjects Su ON SS.SubjectId = Su.SubjecId
)
SELECT StudentId,
MIN(CASE rowno WHEN 1 THEN SubjectName) AS SubjectName,
MIN(CASE rowno WHEN 2 THEN SubjectName) AS SubjectName,
MIN(CASE rowno WHEN 3 THEN SubjectName) AS SubjectName
FROM numbering
GROUP BY StudentId
ORDER BY SubjectId

One could argue that it is better to do this transformation in the client layer.