SQL query table, low-level questions?

1166222

I'm a novice and write out sql statements, remember to give points if I can achieve them. The following are three tables, table 1: student information table, table 2: schedule, table 3: transcript, found out to be like excel:
Student ID, Name, Language, Mathematics, English
201601 Zhangsan 114 120 124
Table 1:
sno sname
201601 Zhang San
Table 2: cno cname
      1 language
      2 Mathematics

Table 3 sno cno grade
    201601 1 114
    201601 2 120
Requires detection of the following:
sno sname 1 2
201601 Zhangsan 114 120

endman

Row-column conversion problem; static can be done with a case statement;
Dynamic (such as yours) may need a string together ^_^

yjws121

This is best achieved using a stored procedure, written in SQL is very readable

72次灌水被抓

Row to column

endman

if exists(select 1 from sysobjects where id = object_id(N't1'))
drop table t1
go

create table t1
(sno varchar(10),
sname varchar(10)
)

if exists(select 1 from sysobjects where id = object_id(N't2'))
drop table t2
go
create table t2
(cno varchar(10),
cname varchar(10)
)


if exists(select 1 from sysobjects where id = object_id(N't3'))
drop table t3
go
create table t3
(sno varchar(10),
cno varchar(10),
grade int
)

insert into t1 values('201601','Zhang San')
insert into t2 values('1','Chinese')
insert into t2 values('2','Math')
insert into t3 values('201601','1',100)
insert into t3 values('201601','2',80)
go

--Execute query
declare @sql varchar(2000)

set @sql ='select t1.sno,t1.sname '
select @SQl = @sql +',sum(case t3.cno when'+ t2.cno + 'then t3.grade else 0 end)' + t2.cname
from t2

set @Sql = @sql + 'from t1, t3 where t1.sno = t3.sno group by t1.sno,t1.sname'
exec( @sql)

1166222

endmancan be achieved, but I am a novice, can you explain why you want to use select 1 from sysobjects where id = object_id(N't1'), and sum(case t3.cno when'+ t2.cno + ' then t3.grade else 0 end) Can I implement it in dephi+access

yourapy

....
It seems that access does not work
I have implemented access, but I can't write it.

1166222

endmancan achieve all the results,
if exists(select 1 from sysobjects where id = object_id(N't1'))
drop table t1
go

create table t1
(sno varchar(10),
sname varchar(10)
)

if exists(select 1 from sysobjects where id = object_id(N't2'))
drop table t2
go
create table t2
(cno varchar(10),
cname varchar(10)
)


if exists(select 1 from sysobjects where id = object_id(N't3'))
drop table t3
go
create table t3
(sno varchar(10),
cno varchar(10),
grade int
)

insert into t1 values('201601','Zhang San')
insert into t1 values('201602','Li Si')
insert into t2 values('1','Chinese')
insert into t2 values('2','Math')
insert into t2 values('3','English')
insert into t3 values('201601','1',100)
insert into t3 values('201601','2',80)
insert into t3 values('201601','3',120)
insert into t3 values('201601','1',111)
insert into t3 values('201601','2',90)
insert into t3 values('201601','3',124)
go

--Execute query
declare @sql varchar(2000)

set @sql ='select t1.sno,t1.sname '
select @SQl = @sql +',sum(case t3.cno when'+ t2.cno + 'then t3.grade else 0 end)' + t2.cname
from t2

set @Sql = @sql + 'from t1, t3 where t1.sno = t3.sno group by t1.sno,t1.sname'
exec( @sql)
Got as follows

sno sname language mathematics english
201601 Zhang San 211 170 244
But what I want is:
sno sname language mathematics english
201601 Zhang San 100 80 120
201602 Li Si 111 90 124

1166222

endmancan be achieved: I am sorry that I inserted it wrong when you inserted it. You will explain the question I asked