SQL Server 2000 Cross Tab Multiple Tables

Question

I have 4 tables that I want to dynamically generate an output but I need tables grade and sport to have its rows converted into columns. The following tables provided have sample data.

student

grade

enrol

sport

and this my desired output:

The output displays the students who enrolled under teacher 91 and shows all the sports (as columns) with its corresponding grade on sy 2014. Again, we are using SQL Server 2000 (and I think I should feel bad about it). I have tried lots of queries I found on the internet but it's not working.. and usually it is statically made (if you already know what columns you want to display).

Cross Tab itself is a little bit handy and I am making it more complicated with multiple tables. I have this problem for over 3 months now and I still haven't succeeded to achieve my desired output. By the way, I am coding it on SqlDataSource in ASP.Net and bind it on a GridView.

Answer 1

SQL 2000 version:

DECLARE @dynamicCols VARCHAR(8000);
SET @dynamicCols = '';

SELECT @dynamicCols = @dynamicCols+ 
       ', SUM(CASE WHEN sport.sportid='''+sportid+''' THEN grade.grade END) AS ['+sport+'] '
FROM sport

EXECUTE ( 
      'SELECT student.idnumber AS ID, student.student AS Student '
      +@dynamicCols+
      'FROM student
       JOIN grade on student.idnumber=grade.idnumber
       JOIN enrol on enrol.sportid=grade.sportid
       JOIN sport on sport.sportid=enrol.sportid 
       WHERE enrol.teacher=''91'' AND enrol.sy=''2014''
       GROUP BY student.idnumber, student.student
       ORDER BY student.student')

---

ORIGINAL ANSWER

OK, since no one has stepped forward I will show you how I would do it dynamically.

Based on YOusaFZai's template of doing it by hand, first you create the dynamic items in the select list, then you merge them to the rest of the select statement, and finally you exec this dynamic statement:

DECLARE @sqlSTR VARCHAR(MAX);
DECLARE @dynamicCols VARCHAR(MAX);

SELECT @dynamicCols = 
(
  SELECT ', SUM(CASE WHEN sport.sportid='''+sportid+''' THEN grade.grade END) AS ['+sport+'] '
  FROM sport
  FOR XML PATH('')
)

--PRINT @dynamicCols

SELECT @sqlSTR = 
      'SELECT student.idnumber AS ID, student.student AS Student '+@dynamicCols+
      'FROM student
       JOIN grade on student.idnumber=grade.idnumber
       JOIN enrol on enrol.sportid=grade.sportid
       JOIN sport on sport.sportid=enrol.sportid 
       WHERE enrol.teacher=''91'' AND enrol.sy=''2014''
       GROUP BY student.idnumber, student.student
       ORDER BY student.student'

EXEC(@sqlSTR)

ADVANCED

Sometimes you might expect bad data and you can protect yourself. For example, if it is possible for the grade table to have sport ids which are not in the sport table you could change the first query like the following to include the bad data in the results:

SELECT @dynamicCols = 
(
  SELECT DISTINCT ', SUM(CASE WHEN grade.sportid='''+grade.sportid+''' THEN grade.grade END) AS ['+ISNULL(sport.sport,'ILLEGAL ID #'+grade.sportid)+'] '
  FROM grade
  LEFT JOIN sport ON sport.sportid = grade.sportid
  FOR XML PATH('')
)