SQL - Dynamic columns

Question

I need to store and show student exam details. Each student may opt for multiple subjects, and each subject may have multiple exams. The following is the table structure I'm using.

I now need to show a single row per student, with each exam constituting a column, basically something as follows.

                     English              Maths
Student Name  Test1   Test2   Test3   Test1   Test2

  Student1     15       20      50     12       18 
  Student2     13       23      50     21       28 

In the above example, English and Maths are subjects added by the user. English has 3 exams and Maths has 2 exams. More subjects/exams may be added by the user, the above table generation needs to be dynamic.

I've got my data setup, but I am not able to retrieve the data in the above format. I'm unable to get the results using the PIVOT statement as it does not accept subqueries (the exams need to be picked up dynamically). TIA.

UPDATE:

I have created a SQLFiddle here with some sample data setup: http://sqlfiddle.com/#!3/b19231/5

This is the PIVOT query I was trying to get the data in the desired format, which does not work:

SELECT * FROM (
SELECT S.StudentId, Sb.SubjectId, E.ExamId,ER.Marks FROM ExamResult ER
INNER JOIN Exam E ON ER.ExamId = E.ExamId
INNER JOIN Subject SB ON SB.SubjectId = E.SubjectId
INNER JOIN Student S ON ER.StudentId = S.StudentId
) as SourceTable
PIVOT 
(
SUM(Marks)
for ExamId in (SELECT DISTINCT Examid FROM Exam INNER JOIN [Subject] ON Exam.SubjectId = [Subject].SubjectId)
) as PivotTable

Note: Please ignore the Grade and SchoolYear columns, I need them for filtering.

Answer 1

SQL Fiddle Demo

You need a dynamic Pivot. I use this sample

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnName AS NVARCHAR(MAX);

SET @DynamicPivotQuery = 'Hola';

SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Exam)
FROM (SELECT DISTINCT SB.Name + ' - ' + E.NAME AS Exam
      FROM ExamResult ER
      INNER JOIN Exam E ON ER.ExamId = E.ExamId
      INNER JOIN Subject SB ON SB.SubjectId = E.SubjectId
      INNER JOIN Student S ON ER.StudentId = S.StudentId) AS Courses;

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT StudentName, ' + @ColumnName + '
    FROM (
          SELECT S.Name AS StudentName, SB.Name + '' - '' + E.NAME AS Exam, ER.Marks 
          FROM ExamResult ER
          INNER JOIN Exam E ON ER.ExamId = E.ExamId
          INNER JOIN Subject SB ON SB.SubjectId = E.SubjectId
          INNER JOIN Student S ON ER.StudentId = S.StudentId
          ) as T
    PIVOT(SUM(Marks) 
          FOR Exam IN (' + @ColumnName + ')) AS PVTTable';

EXEC sp_executesql @DynamicPivotQuery;
GO

OUTPUT

Your fiddle data miss a exam for student 2, so a null is show in result

| StudentName | ENGLISH - TEST1 | ENGLISH - TEST2 | ENGLISH - TEST3 | MATHS - TEST1 | MATHS - TEST2 |
|-------------|-----------------|-----------------|-----------------|---------------|---------------|
|    STUDENT1 |              10 |              10 |              10 |            10 |            10 |
|    STUDENT2 |              15 |              15 |              15 |        (null) |            15 |