How to create/edit/delete SQL server tables dynamically in asp.net web forms?

Question

I am using VS 2008 (asp.net with c#) and SQL Server 2005....

Subject table:

create table subject
(subid varchar(10),
subname varchar(20))

sample data:

subid           subname

1               subject1
2               subject2
3               subject3

=> it includes all the subjects' details in each row, for example:

Result table: Sampledata

studentid     subid        subname             marks

1             1            subject1            12
1             2            subject2            23
1             3            subject3            22
1             4            subject4            20

I want something like this:

studentid     subject1    subject2    subject3    subject4

1             12          23          22           20

2

3

So, I want to display each subject with its marks in column wise...

If I create a static table like...

create table subject    
(subject1 varchar(20),
 subject2 varchar(20),
 subject3 varchar(20))

NOTE: Then it's not possible because the subjects might change per semester (in subject table). That's why I want to create/edit/delete tables dynamically within an ASP.NET web form.

Answer 1

A couple of things. First, in the results table, the subject name is not necessary, and actually de-normalizes the database. Should be like this:

studentid     subid        marks

1             1            12
1             2            23
1             3            22
1             4            20

Now, to query:

SELECT s.subjname, r.studentid, r.marks
FROM subject s
    INNER JOIN result r on s.subid = r.subid

To get the table you want, listing out each student's marks, use a pivot query like this:

SELECT studentid, 
[Subject1], [Subject2], [Subject3], [Subject4]
FROM
(SELECT s.subjname, r.studentid, r.marks
    FROM subject s
        INNER JOIN result r on s.subid = r.subid
) AS SourceTable
PIVOT
(
AVG(marks)
FOR subjname IN ([Subject1], [Subject2], [Subject3], [Subject4])
) AS PivotTable;

You can make the pivot query dynamic as needed. Not necessary to construct dynamic tables on the fly.