CODEWITHSUNDEEP
sql-servert-sql
Maestro1024
Maestro1024

Reputation: 3293

Create a SQL table with a varying number of columns

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 to 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.

Upvotes: 3

Views: 1273

Answers (4)

Jane T
Jane T

Reputation: 2091

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..

Upvotes: 4

SQLMenace
SQLMenace

Reputation: 135181

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

Upvotes: 3

user114600
user114600

Reputation:

It's very clunky.

Have three tables:

  1. Teacher
  2. Student
  3. TeacherStudent, which is a many-many table to join the two.

Do the pivot method expressed in the other answer. It makes more sense.

Upvotes: 2

Denis Valeev
Denis Valeev

Reputation: 6015

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, ...

Upvotes: 3

Related Questions