--生成测试数据
create table #student(主键编号 int,姓名 varchar(20))
insert into #student select 1230001,'张三'
insert into #student select 1230002,'李四'
insert into #student select 1230003,'王五'create table #subject(主键编号 int,报考科目 varchar(10))
insert into #subject select 1230001,'00002'
insert into #subject select 1230001,'00004'
insert into #subject select 1230002,'00001'
insert into #subject select 1230002,'00002'
insert into #subject select 1230002,'00005'
insert into #subject select 1230003,'00003'
--执行查询
declare @s varchar(8000)
set @s = ''
select
@s = @s + ',['+报考科目+']=max(case when 报考科目='''+报考科目+''' then 1 end)'
from
#subject
group by
报考科目
order by
报考科目set @s = ' select a.主键编号,a.姓名'+@s+' from #student a,#subject b '
+ ' where a.主键编号=b.主键编号 '
+ ' group by a.主键编号,a.姓名 '
+ ' order by a.主键编号'exec(@s)--输出结果
/*
主键编号 姓名 00001 00002 00003 00004 00005
------- ------ ------ ------ ------ ------ ------
1230001 张三 NULL 1 NULL 1 NULL
1230002 李四 1 1 NULL NULL 1
1230003 王五 NULL NULL 1 NULL NULL
*/
create table #student(主键编号 int,姓名 varchar(20))
insert into #student select 1230001,'张三'
insert into #student select 1230002,'李四'
insert into #student select 1230003,'王五'create table #subject(主键编号 int,报考科目 varchar(10))
insert into #subject select 1230001,'00002'
insert into #subject select 1230001,'00004'
insert into #subject select 1230002,'00001'
insert into #subject select 1230002,'00002'
insert into #subject select 1230002,'00005'
insert into #subject select 1230003,'00003'
--执行查询
declare @s varchar(8000)
set @s = ''
select
@s = @s + ',['+报考科目+']=max(case when 报考科目='''+报考科目+''' then 1 end)'
from
#subject
group by
报考科目
order by
报考科目set @s = ' select a.主键编号,a.姓名'+@s+' from #student a,#subject b '
+ ' where a.主键编号=b.主键编号 '
+ ' group by a.主键编号,a.姓名 '
+ ' order by a.主键编号'exec(@s)--输出结果
/*
主键编号 姓名 00001 00002 00003 00004 00005
------- ------ ------ ------ ------ ------ ------
1230001 张三 NULL 1 NULL 1 NULL
1230002 李四 1 1 NULL NULL 1
1230003 王五 NULL NULL 1 NULL NULL
*/
[科目_1]=max(case b.报考科目 when '00001' then 报考科目 end),
[科目_2]=max(case b.报考科目 when '00002' then 报考科目 end),
[科目_3]=max(case b.报考科目 when '00003' then 报考科目 end),
[科目_4]=max(case b.报考科目 when '00004' then 报考科目 end),
[科目_5]=max(case b.报考科目 when '00005' then 报考科目 end)
from table1 a join table2 b on a.主键编号=b.主键编号
group by a.主键编号,a.姓名
insert into #student select 1230001,'张三'
insert into #student select 1230002,'李四'
insert into #student select 1230003,'王五'create table #subject(主键编号 int,报考科目 varchar(10))
insert into #subject select 1230001,'00002'
insert into #subject select 1230001,'00004'
insert into #subject select 1230002,'00001'
insert into #subject select 1230002,'00002'
insert into #subject select 1230002,'00005'
insert into #subject select 1230003,'00003'go
select a.主键编号,a.姓名,
[科目_1]=max(case b.报考科目 when '00001' then 1 end),
[科目_2]=max(case b.报考科目 when '00002' then 1 end),
[科目_3]=max(case b.报考科目 when '00003' then 1 end),
[科目_4]=max(case b.报考科目 when '00004' then 1 end),
[科目_5]=max(case b.报考科目 when '00005' then 1 end)
from #student a left join #subject b on a.主键编号=b.主键编号
group by a.主键编号,a.姓名/*
主键编号 姓名 科目_1 科目_2 科目_3 科目_4 科目_5
----------- -------------------- ----------- ----------- ----------- ----------- -----------
1230002 李四 1 1 NULL NULL 1
1230003 王五 NULL NULL 1 NULL NULL
1230001 张三 NULL 1 NULL 1 NULL(所影响的行数为 3 行)
*/
drop table #student,#subject
select a.主键编号,a.姓名,
[科目_1]=max(case b.报考科目 when '00001' then 报考科目 end),
[科目_2]=max(case b.报考科目 when '00002' then 报考科目 end),
[科目_3]=max(case b.报考科目 when '00003' then 报考科目 end),
[科目_4]=max(case b.报考科目 when '00004' then 报考科目 end),
[科目_5]=max(case b.报考科目 when '00005' then 报考科目 end)
from table1 a join table2 b on a.主键编号=b.主键编号
group by a.主键编号,a.姓名
select a.主键编号,max(a.姓名) as 姓名 ,
[科目_1]=max(case b.报考科目 when '00001' then '1' else '' end),
[科目_2]=max(case b.报考科目 when '00002' then '1' else '' end),
[科目_3]=max(case b.报考科目 when '00003' then '1' else '' end),
[科目_4]=max(case b.报考科目 when '00004' then '1' else '' end),
[科目_5]=max(case b.报考科目 when '00005' then '1' else '' end)
from table1 a join table2 b on a.主键编号=b.主键编号
group by a.主键编号
今天第一次到这问问题各位就这么热心,谢谢!怪我没说清楚,我不想用存储过程,但还是谢谢 libin_ftsafe(子陌红尘)和 wgsasd311(自强不息)
--------------------------------------------------
偶用的是T-SQL的动态SQL语句,并没有封装成存储过程。
create table Stud_Subj_Info
(
StudNo char(10) not null unique,
StudName char(10) unique,
Subj1 char(10),
Subj2 char(10),
Subj3 char(10),
Subj4 char(10),
Subj5 char(10)
)
--收集信息
Insert into Stud_Subj_Info(StudNo,StudName,Subj1,Subj2,Subj3,Subj4,Subj5)
select a.StudNo,a.StudName,b.Subj1,c.Subj2,d.Subj3,e.Subj4,f.Subj5
from StudentInfo a left join (select StudNo,Subj1='1' from SubjectInfo where SubjNo = '00001') as b
on a.StudNo = b.StudNo left join(select StudNo,Subj2='1' from SubjectInfo where SubjNo= '00002') as c
on a.StudNo = c.StudNo left join(select StudNo,Subj3='1' from SubjectInfo where SubjNo= '00003') as d
on a.StudNo = d.StudNo left join(select StudNo,Subj4='1' from SubjectInfo where SubjNo= '00004') as e
on a.StudNo = e.StudNo left join(select StudNo,Subj5='1' from SubjectInfo where SubjNo= '00005') as f
on a.StudNo = f.StudNo
1230002 李四 1 1 NULL NULL 1
1230003 王五 NULL NULL 1 NULL NULL
這是結果...
不知道你是否像這樣寫...
姓名,
(select 报考科目
from #subject
where #subject.主键编号=Tb.主键编号
and 报考科目='00001') as 科目1,
(select 报考科目
from #subject
where #subject.主键编号=Tb.主键编号
and 报考科目='00002') as 科目2,
(select 报考科目
from #subject
where #subject.主键编号=Tb.主键编号
and 报考科目='00003') as 科目3,
(select 报考科目
from #subject
where #subject.主键编号=Tb.主键编号
and 报考科目='00004') as 科目4,
(select 报考科目
from #subject
where #subject.主键编号=Tb.主键编号
and 报考科目='00005') as 科目5
from
(
select distinct A.主键编号,
A.姓名
from #student A,#subject B
where A.主键编号=B.主键编号
) Tb
----------------------------------------