## Practice

0

(click on this box to dismiss)

Q&A for serious players and enthusiasts of chess

``````CREATE table #Courses
([course_id] varchar(20), [course_name] varchar(20))
;

INSERT INTO #Courses
([course_id], [course_name])
VALUES
('csc108', 'Programming'),
('mat137', 'Calculus')
;

CREATE table #Students
([student_id] int, [student_name] varchar(20))
;

INSERT INTO #Students
([student_id], [student_name])
VALUES
(1, 'John Smith'),
(2, 'Jane Doe')
;

CREATE table #Marks
([student_id] int, [course_id] varchar(20), [mark] int)
;

INSERT INTO #Marks
([student_id], [course_id], [mark])
VALUES
(1, 'mat137', 90),
(2, 'mat137', 83),
(1, 'csc108', 75)
;

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(course_id)
from #Courses
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = N'SELECT student_id, student_name, ' + @cols + N' from
(
Select
#Courses.course_id,
#Marks.mark,
#Marks.student_id,
#Students.student_name
FROM #Courses
INNER JOIN #Marks
ON #Marks.course_id = #Courses.course_id
INNER JOIN #Students
ON #Students.student_id = #Marks.student_id
) x
pivot
(
max(mark)
for course_id in (' + @cols + N')
) p '

exec sp_executesql @query;``````

### Enter Parameters

Options:
-
Switch to meta site
Hold tight while we fetch your results
:records returned in :time ms:cached