Practice

0

Please login or register to vote for this query.

(click on this box to dismiss)

Chess

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)
;
/*
Select
*
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
  ) as source
  PIVOT 
  (
  MAX(mark)
  FOR source.course_id in ([csc108], [mat137])
  ) p
  */
  

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')
            ) piv '

exec sp_executesql @query;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results