Create a SQL table with a varying number of columns

Question

I have two tables, say teacher and student. I want to build a third table called class. The class table will have one column for the teacher of the class. But I want too see if their is an elegant way to represent the students. My first thought is to have say 30 columns.. student1, student2 and have quids for each of these that tie back to a row in the student table.

But I am asking to see if there is a more preferred solution. The above solution seems clunky.

Answer 1

The class "table" is not a table but a result of running a stored procedure with pivoting of data.

And data structure is as follows:

Student: Id, ...
Teacher: Id, ...
StudentClass: StudentId, ClassId, ...
Class: Id, TeacherId, ...

Answer 2

SQL Server has this thing called sparse columns However I would advice against it. Normalize your data and then PIVOT/crosstab the results when you want to display it side by side

Answer 3

If it was me I would have a 4th table called attendees or similar which links the students to the class as it's a many to many relationship. Which would contain the Class ID and the Student ID as a minimum..