create table #(a varchar(100),b int) insert # values('aa',11) insert # values('bb',1) insert # values('aa',45) insert # values('cc',81) insert # values('a',11) insert # values('aay',561) insert # values('a',14)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 aselect @sql = left(@sql,len(@sql)-1) + ' from #'exec(@sql)drop table #
declare @sql varchar(8000) set @sql = 'select 流水号,商店' select @sql = @sql + ',case 商品名称 when '''+商品名称+''' then 销售额 else 0 end ['+商品名称+']' from (select distinct 商品名称 from 你的表) aexec(@sql+',日期 from 你的表 ')
create proc 过程名 @日期参数 datetime as declare @sql varchar(8000) set @sql = 'select 流水号,商店' select @sql = @sql + ',case 商品名称 when '''+商品名称+''' then 销售额 else 0 end ['+商品名称+']' from (select distinct 商品名称 from 你的表 where 日期=@日期参数) a set @sql=@sql+',日期 from 你的表 where 日期='''+CONVERT(varchar(10),@日期参数,120) +'''' exec(@sql)
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 流水号,商店,日期' exec(@sql)
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 @sqlexec(@sql) go exec testdrop table 销售记录 drop procedure test
select 流水号,商店, sum((case when 商品名称 = '苹果' then 销售额 else 0 end)) as 苹果, sum((case when 商品名称 = '梨' then 销售额 else 0 end)) as 梨, sum((case when 商品名称 = '香蕉' then 销售额 else 0 end)) as 香蕉, 日期 from tablename group by 日期,商店
create FUNCTION f_convert(@startDate datetime,@endDate datetime) returns @report table (流水号 varchar(20), 商店 varchar(20), 苹果 numeric(10,2), 梨 numeric(10,2), 香蕉 numeric(10,2), 日期 datetime) AS begininsert into @report select 流水号,商店, sum((case when 商品名称 = '苹果' then 销售额 else 0 end)) as 苹果, sum((case when 商品名称 = '梨' then 销售额 else 0 end)) as 梨, sum((case when 商品名称 = '香蕉' then 销售额 else 0 end)) as 香蕉, 日期 from tablename group by 流水号,日期,商店 where 日期 between @startDate and @endDate return endGO
如果你的商品名称不固定的话,用函数是不行的.我写了一个存储过程,可以达到你的要求 此存储对于商品无限制,你有多少商品,就会动态生成相应的表--创建数据测试环境 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 set nocount on 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 流水号,商店,日期' exec(@sql) set nocount off go--测试所写的存储过程 exec test --删除数据测试环境. drop table 销售记录 drop procedure test
create proc 过程名 @日期参数 datetime as declare @sql varchar(8000) set @sql = 'select 流水号,商店' select @sql = @sql + ',case 商品名称 when '''+商品名称+''' then 销售额 else 0 end ['+商品名称+']' from (select distinct 商品名称 from 你的表 where 日期=@日期参数) a set @sql=@sql+',日期 from 你的表 where 日期='''+CONVERT(varchar(10),@日期参数,120) +'''' exec(@sql) 有错吗??你把中文的部分换成你的表试试。不能得出结果吗???????????
create proc 过程名 @日期参数 datetime as declare @sql varchar(8000) set @sql = 'select 流水号,商店' select @sql = @sql + ',case 商品名称 when '''+商品名称+''' then 销售额 else 0 end ['+商品名称+']' from (select distinct 商品名称 from 你的表 where 日期=@日期参数) a set @sql=@sql+',日期 from 你的表 where 日期='''+CONVERT(varchar(10),@日期参数,120) +'''' exec(@sql) 有错吗??你把中文的部分换成你的表试试。不能得出结果吗???????????
insert # values('aa',11)
insert # values('bb',1)
insert # values('aa',45)
insert # values('cc',81)
insert # values('a',11)
insert # values('aay',561)
insert # values('a',14)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 aselect @sql = left(@sql,len(@sql)-1) + ' from #'exec(@sql)drop table #
set @sql = 'select 流水号,商店'
select @sql = @sql + ',case 商品名称 when '''+商品名称+''' then 销售额 else 0 end ['+商品名称+']'
from (select distinct 商品名称 from 你的表) aexec(@sql+',日期 from 你的表 ')
@日期参数 datetime
as
declare @sql varchar(8000)
set @sql = 'select 流水号,商店'
select @sql = @sql + ',case 商品名称 when '''+商品名称+''' then 销售额 else 0 end ['+商品名称+']'
from (select distinct 商品名称 from 你的表 where 日期=@日期参数) a
set @sql=@sql+',日期 from 你的表 where 日期='''+CONVERT(varchar(10),@日期参数,120) +''''
exec(@sql)
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 流水号,商店,日期'
exec(@sql)
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 @sqlexec(@sql)
go
exec testdrop table 销售记录
drop procedure test
sum((case when 商品名称 = '苹果' then 销售额 else 0 end)) as 苹果,
sum((case when 商品名称 = '梨' then 销售额 else 0 end)) as 梨,
sum((case when 商品名称 = '香蕉' then 销售额 else 0 end)) as 香蕉,
日期
from tablename
group by 日期,商店
returns @report table
(流水号 varchar(20),
商店 varchar(20),
苹果 numeric(10,2),
梨 numeric(10,2),
香蕉 numeric(10,2),
日期 datetime)
AS
begininsert into @report
select 流水号,商店,
sum((case when 商品名称 = '苹果' then 销售额 else 0 end)) as 苹果,
sum((case when 商品名称 = '梨' then 销售额 else 0 end)) as 梨,
sum((case when 商品名称 = '香蕉' then 销售额 else 0 end)) as 香蕉,
日期
from tablename
group by 流水号,日期,商店
where 日期 between @startDate and @endDate
return
endGO
此存储对于商品无限制,你有多少商品,就会动态生成相应的表--创建数据测试环境
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
set nocount on
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 流水号,商店,日期'
exec(@sql)
set nocount off
go--测试所写的存储过程
exec test
--删除数据测试环境.
drop table 销售记录
drop procedure test
@日期参数 datetime
as
declare @sql varchar(8000)
set @sql = 'select 流水号,商店'
select @sql = @sql + ',case 商品名称 when '''+商品名称+''' then 销售额 else 0 end ['+商品名称+']'
from (select distinct 商品名称 from 你的表 where 日期=@日期参数) a
set @sql=@sql+',日期 from 你的表 where 日期='''+CONVERT(varchar(10),@日期参数,120) +''''
exec(@sql)
有错吗??你把中文的部分换成你的表试试。不能得出结果吗???????????
@日期参数 datetime
as
declare @sql varchar(8000)
set @sql = 'select 流水号,商店'
select @sql = @sql + ',case 商品名称 when '''+商品名称+''' then 销售额 else 0 end ['+商品名称+']'
from (select distinct 商品名称 from 你的表 where 日期=@日期参数) a
set @sql=@sql+',日期 from 你的表 where 日期='''+CONVERT(varchar(10),@日期参数,120) +''''
exec(@sql)
有错吗??你把中文的部分换成你的表试试。不能得出结果吗???????????
两者的方法基本上一样.
大力的方法,当没有数据时,显示为0,而且日期以yyyy-mm-dd格式显示我的方法,当没有数据时,显示为NULL,日期是原样显示的,没有处理.