Question

我正在使用VS 2008(asp.net with c#)和SQL服务器2005。

http://www.hchr.org。

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

样本数据:

subid           subname

1               subject1
2               subject2
3               subject3

=> 它包括每一行的所有主题细节,例如:

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

因此,我想在英字母栏中用<代码>表示每个主题。

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

两件事。首先,在results的表格中,主题名称并不必要,实际上使数据库脱节。也应如此:

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

找到你想要的桌子,列出每个学生的分数,使用像样的 p子:

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.

友情链接