统计列表如下
表中有：
Name sid aid
aaa 1 1
aaa 2 1
aaa 3 2
bbb 1 2
bbb 2 3
ccc 1 2
ccc 2 5
ccc 3 2
......
什么语句可以形成：
Name aid1 aid2 aid3
aaa 1 1 2
bbb 2 3
ccc 2 5 2
......
[解决办法]
select name,sum(case when sid=1 then aid else 0 end)aid1,
sum(case when sid=2 then aid else 0 end)aid2,
sum(case when sid=3 then aid else 0 end)aid3
from 表 group by name
[解决办法]
create table test(name varchar(10),sid int,aid int)
insert test select 'aaa ',1,1
union all select 'aaa ',2,1
union all select 'aaa ',3,2
union all select 'bbb ',1,2
union all select 'bbb ',2,3
union all select 'ccc ',1,2
union all select 'ccc ',2,5
union all select 'ccc ',3,2
declare @s varchar(8000)
set @s= 'select name '
select @s=@s+ ',max(case bh when ' ' '+rtrim(bh)+ ' ' ' then aid else 0 end) as aid '+rtrim(bh)+ ' '
from (select *,bh=(select count(1) from test where name=b1.name and sid <=b1.sid) from test b1)t group by bh
select @s=@s+ ' from (select *,bh=(select count(1) from test where name=b1.name and sid <=b1.sid) from test b1)t group by name '
exec(@s)
drop table test
[解决办法]
create table T(Name nvarchar(10),sid int,aid int)
insert T select 'aaa ', 1, 1
union all select 'aaa ', 2, 1
union all select 'aaa ', 3, 2
union all select 'bbb ', 1, 2
union all select 'bbb ', 2, 3
union all select 'ccc ', 1, 2
union all select 'ccc ', 2, 5
union all select 'ccc ', 3, 2
declare @sql nvarchar(4000)
set @sql= 'select name, '
select @sql=@sql+quotename( 'aid '+rtrim(sid))+ '=max(case when sid= '+rtrim(sid)+ ' then aid else 0 end), '
from T
group by sid
select @sql=left(@sql,len(@sql)-1), @sql=@sql+ ' from T group by name '
exec(@sql)
--result
name aid1 aid2 aid3
---------- ----------- ----------- -----------
aaa 1 1 2
bbb 2 3 0
ccc 2 5 2
[解决办法]
create table t(xh varchar(20),kc varchar(20),cj int)
insert t select '051000333 ', '高等数学 ',55
union all select '051000333 ', '大学语文 ',67
union all select '051000333 ', '经济学基础 ',88
union all select '021000224 ', '高等数学 ',64
union all select '021000224 ', '大学语文 ',32
union all select '021000224 ', '经济学基础 ',75
union all select '041000851 ', '高等数学 ',69
union all select '041000851 ', '大学语文 ',75
union all select '041000851 ', '经济学基础 ',65
declare @sql varchar(8000)
set @sql = 'select xh '
select @sql = @sql + ' , sum(case kc when ' ' ' + kc + ' ' ' then cj else 0 end) [ ' + kc + '] '
from (select distinct kc from t) as a
set @sql = @sql + ' from t group by xh '
exec(@sql)
给你看一下我做和例子！
[解决办法]
create table test(name varchar(10),sid int,aid int)
insert test select 'aaa ',1,1
union all select 'aaa ',2,1
union all select 'aaa ',3,2
union all select 'bbb ',1,2
union all select 'bbb ',2,3
union all select 'ccc ',1,2
union all select 'ccc ',2,5
union all select 'ccc ',3,2
select name ,isnull(max(case when sid=1 then aid else null end), ' ') as aid1,
isnull(max(case when sid=2 then aid else null end), ' ') as aid2,
isnull(max(case when sid=3 then aid else null end), ' ') as aid3
from test
group by name
declare @sql varchar(8000)
set @sql= 'select name '
select @sql=@sql+ ', isnull(max(case when sid= ' ' '+rtrim(sid)+ ' ' ' then aid else null end), ' ' ' ') as aid '+rtrim(sid)
from (select distinct sid from test) aa
set @sql=@sql+ ' from test group by name '
exec(@sql)
name aid1 aid2 aid3
---------- ----------- ----------- -----------
aaa 1 1 2
bbb 2 3 0
ccc 2 5 2
[解决办法]
create proc pp
@tbname sysname
as
declare @sql varchar(8000)
set @sql= 'select Name,aid1=sum(case when sid=1 then aid else null end),aid2=sum(case when sid=2 then aid else null end),aid3=sum(case when sid=3 then aid else null end) from '+@tbname+ ' group by Name '
exec(@sql)
go
--执行
exec pp 'test '
