declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + 'sum(case a when '''+a+'''
then b else 0 end) '+a+'的数量,'
from (select distinct a from #) as a
create table #leaguer
(
学生编号 int foreign key references dbo.#student([学生编号]) on delete cascade on update cascade,
社团编号 int foreign key references dbo.#league([社团编号]) on delete cascade on update cascade primary key (学生编号,社团编号)
)
insert into #student values('lxl')
insert into #student values('abc')
insert into #student values('88')
insert into #student values('99')
insert into #league values('体育协会')
insert into #league values('音乐协会')
insert into #league values('美术协会')
insert into #league values('科技协会')
insert into #league values('英语协会')
insert into #league values('计算机协会')
insert into #leaguer values(1,4)
insert into #leaguer values(3,2)
insert into #leaguer values(3,5)
insert into #leaguer values(1,2)
insert into #leaguer values(3,4)
insert into #leaguer values(1,3)
insert into #leaguer values(3,1)
insert into #leaguer values(2,1)
insert into #leaguer values(2,5)
declare @sql varchar(8000),@i int,@sql2 varchar(8000)
select @i=0,@sql = 'select 姓名',@sql2=''
select @i=@i+1,@sql = @sql + ',max(case 社团编号 when '''+cast(社团编号 as varchar(10))+''' then 社团名称 end) [社团名称'+cast(@i as varchar(10))+']'
,@sql2=@sql2+',cast(count(社团名称'+cast(@i as varchar(10))+') as varchar(100))'
from #league
select @sql = @sql+' into #a from (select a.姓名,b.社团编号,b.社团名称 from #student a,#league b,#leaguer c where a.学生编号=c.学生编号 and b.社团编号=c.社团编号) tem group by 姓名'
exec(@sql+' select * from #a union all select ''合计'''+@sql2+' from #a')
declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + 'sum(case a when '''+a+'''
then b else 0 end) '+a+'的数量,'
from (select distinct a from #) as a
create table 销售记录(流水号 int,商店 varchar(20), 商品名称 varchar(100), 销售额 money,日期 datetime)
insert into 销售记录(流水号,商店,商品名称,销售额,日期)
values(1,'甲','苹果',100.00,'2003-1-1')
insert into 销售记录(流水号,商店,商品名称,销售额,日期)
values(2,'乙','苹果',10.00,'2003-1-1')
insert into 销售记录(流水号,商店,商品名称,销售额,日期)
values(3,'丙','梨',200.00,'2003-1-1')
insert into 销售记录(流水号,商店,商品名称,销售额,日期)
values(4,'丁','香蕉',300.00,'2003-1-1')
go
create procedure test
as
declare @sql varchar(2000),@fdname varchar(100)
select @sql=''
declare #aa cursor for select distinct 商品名称 from 销售记录
open #aa
fetch next from #aa into @fdname
while @@fetch_status=0
begin
select @sql=@sql+',sum(case when 商品名称=''' +@fdname+''' then 销售额 end) as ['+@fdname+']'
fetch next from #aa into @fdname
end
close #aa
deallocate #aa
select @sql='select 流水号,商店,'+right(@sql,len(@sql)-1)+',日期 from 销售记录 group by 流水号,商店,日期'
print @sql
举个例子:
--1.产品表(CPB):
create table cpb(商品编号 varchar(1),名称 varchar(4),规格 varchar(6))
insert into cpb values('a','甲鱼','S-23')
insert into cpb values('b','鲤鱼','D-9')
insert into cpb values('c','鲇鱼','F-2')
insert into cpb values('d','带鱼','R-20')
go
--2.日记帐表(RJZ)
create table rjz(商品编号 varchar(1),时间 varchar(6),入库额 int,出库额 int)
insert into rjz values('a','星期一',10,9)
insert into rjz values('b','星期二',5,4)
insert into rjz values('c','星期一',10,9)
insert into rjz values('a','星期二',5,4)
insert into rjz values('b','星期一',10,9)
insert into rjz values('a','星期三',5,4)
insert into rjz values('a','星期五',5,0)
--得到要处理数据的表
select id=identity(int,1,1),name
into #temp
from
(select '星期一' as name
union all select '星期二'
union all select '星期三'
union all select '星期四'
union all select '星期五'
union all select '星期六'
union all select '星期日') a
declare #aa cursor for
select distinct 时间,id
from rjz a,#temp b
where a.时间=b.name
and b.id between
(select id from #temp where name=@start)
and
(select id from #temp where name=@end)
order by b.id
select @sql='select 商品编号 ',@filter='',@fdname=''
open #aa
fetch next from #aa into @fd,@i
while @@fetch_status=0
begin
select @filter=@filter+''''+@fd+''','
,@fdname=@fdname+',b.['+@fd+'-入],b.['+@fd+'-出]'
, @sql=@sql
+char(13)+' ,sum(case 时间 when '''+@fd
+''' then 入库额 else 0 end) as ['+@fd+'-入]'
+char(13)+' ,sum(case 时间 when '''
+@fd+''' then 出库额 else 0 end) as ['+@fd+'-出]'
fetch next from #aa into @fd,@i
end
close #aa
deallocate #aa
set @sql='select a.名称 as 商品名称,a.规格'+@fdname
+char(13)+' from cpb a,('
+@sql+char(13)+' from rjz'
+char(13)+' where 时间 in('
+left(@filter,len(@filter)-1)+')'
+char(13)+' group by 商品编号) b'
+char(13)+'where a.商品编号=b.商品编号'
--print @sql
exec(@sql)
go
--测试
exec pbuild '星期一','星期三'
go
--删除测试环境
drop table cpb,rjz
drop procedure pbuild
Select ZH,
sum(case when JYDM = '9999' then JYJE else 0 end) as 9999交易总金额,
sum(case when JYDM = '9998' then JYJE else 0 end) as 9998交易总金额,
sum(case when JYDM = '9995' then JYJE else 0 end) as 9995交易总金额
from yourTable group by ZH
OR:
declare @sql varchar(8000)
set @sql = 'select ZH,'
select @sql = @sql + 'sum(case JYDM when '''+JYDM+'''
then JYJE else 0 end) '+JYDM+'交易总金额,'
from (select distinct JYDM from 你的表) as a
select @sql = left(@sql,len(@sql)-1) + ' from 你的表'