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.

Can you add sample data and the queries you've tried, even though they might not be what you need? Any chance you could set up a sql fiddle @ sqlfiddle.com? — Rachel Ambler
– Rachel Ambler, Commented Sep 19, 2015 at 23:02
@RachelAmbler added the fiddle and the PIVOT query I was trying. — Fahad
– Fahad, Commented Sep 19, 2015 at 23:22
The trouble with this is going to be the format you specified. Can each column be labeled such as "English-Test1" "English-Test2" etc? — Rachel Ambler
– Rachel Ambler, Commented Sep 19, 2015 at 23:34
When the query is compiled and executed, the shape of the result set must be defined and known even when PIVOT is used. Your only option is to build the query string dynamically. — cynic
– cynic, Commented Sep 19, 2015 at 23:35

Juan Carlos Oropeza · Accepted Answer · 2015-09-19 23:47:54Z

2

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 |

answered Sep 19, 2015 at 23:47

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rachel Ambler Over a year ago

Dang rat it - beat me to it. This was the same way I was going - ala dbaspot.com/sqlserver-programming/…. Give the man his magic beanz - he earned them!

Fahad Over a year ago

Thanks, this'll work!! And that missing data was intentional. :)

Collectives™ on Stack Overflow

SQL - Dynamic columns

1 Answer 1

OUTPUT

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

OUTPUT

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related