"如何将下面第一个表转置成第二个表?
请注意下表中的项目数是随上表包含的科目数变的。如果再多一科,比如wy,结果集也应多出一项wy"
cj1
xm km fs
a yw 1
a sx 2
b yw 1
c yw 1
c sx 2
cj2
xm yw sx zf
a 1 2 3
b 1 0 1
c 1 2 3
建表代码已写在下面:
create table cj1 (xm varchar(8),km varchar(4),fs float)
insert cj1 select "a","yw",1 union
select "a","sx",2 union
select "a","yw",1 union
select "a","yw",1 union
select "a","sx",2
请注意下表中的项目数是随上表包含的科目数变的。如果再多一科,比如wy,结果集也应多出一项wy"
cj1
xm km fs
a yw 1
a sx 2
b yw 1
c yw 1
c sx 2
cj2
xm yw sx zf
a 1 2 3
b 1 0 1
c 1 2 3
建表代码已写在下面:
create table cj1 (xm varchar(8),km varchar(4),fs float)
insert cj1 select "a","yw",1 union
select "a","sx",2 union
select "a","yw",1 union
select "a","yw",1 union
select "a","sx",2
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (xm varchar(1),km varchar(2),fs int)
insert into #T
select 'a','yw',1 union all
select 'a','sx',2 union all
select 'b','yw',1 union all
select 'c','yw',1 union all
select 'c','sx',2declare @cols nvarchar(4000)
select @cols=isnull(@cols+',','')+'['+km+']=sum(case km when '''+km+''' then fs else 0 end)' from #T group by km
exec ('select xm,'+@cols+',zf=sum(fs) from #T group by xm')/*
xm sx yw zf
---- ----------- ----------- -----------
a 2 1 3
b 0 1 1
c 2 1 3
*/
高!
Thanks a million!