--那个01,02之类的是什么? 日期吗?declare @s varchar(8000),@i int
select @s='',@i=1
while @i<32
select @s=@s+',['+right('0'+cast(@i as varchar),2)+']=sum(case dd when '+cast(@i as varchar)+' then [money] else 0 end)'
,@i=@i+1
exec('select 日期,姓名'+@s+' from(
select 日期=replace(convert(varchar(7),FDateTime,120),''-'',''年'')+''月'',姓名,[money],dd=day(FDateTime) from 消费数据)a group by 日期,姓名')
select @s='',@i=1
while @i<32
select @s=@s+',['+right('0'+cast(@i as varchar),2)+']=sum(case dd when '+cast(@i as varchar)+' then [money] else 0 end)'
,@i=@i+1
exec('select 日期,姓名'+@s+' from(
select 日期=replace(convert(varchar(7),FDateTime,120),''-'',''年'')+''月'',姓名,[money],dd=day(FDateTime) from 消费数据)a group by 日期,姓名')
select @s='',@i=1
while @i<32
select @s=@s+',['+right('0'+cast(@i as varchar),2)+']=sum(case dd when '+cast(@i as varchar)+' then [money] else 0 end)'
,@i=@i+1
exec('select 日期,姓名'+@s+' from(
select 日期=convert(varchar(10),FDateTime,120),姓名=name
,[money],dd=day(FDateTime)
from 消费数据
)a group by 日期,姓名')
select FDateTime as 日期,Name as 姓名,sum(case mach when '01' then money end) as [01],sum(case mach when '02' then money end)as [02],sum(case mach when '03' then money end) as [03],sum(case mach when '04' then money end) as [04]
from table group by fdatetime,name
还有一种方法是:
declare @sql varchar(8000)
set @sql='select FDateTime as 日期,Name as 姓名'
select @sql=@sql+',['+mach+']=sum(case mach when '''+mach+''' then money end) from table group by fdatetime,name' from (select distinct mach from table)
exec(@sql)
declare @s varchar(8000)set @s=''select6 @s=@s + ', sum( case when mach=''' + mach + ''' then money else 0 end ) as ['+mach+'] '
from tablename --表名换成你实际的名字。
group by machselect @s='select convert(varchar(10),FDateTime,120), [Name] '+@s
+ ' from tablename group by convert(varchar(10),fdatetime,120),[name]'exec(@s)
select id=identity(int,1,1),* into #t from 消费数据--查询
declare @s varchar(8000),@i int
select @s='',@i=max(ii)
from(
select ii=count(*) from 消费数据 group by name) a
while @i>0
select @s=',['+right('0'+cast(@i as varchar),2)+']=sum(case dd when '+cast(@i as varchar)+' then [money] else 0 end)'+@s
,@i=@i-1
exec('select 日期,姓名'+@s+' from(
select 日期=convert(varchar(10),FDateTime,120),姓名=name
,[money],dd=(select sum(1) from #t where name=a.name and id<=a.id)
from #t a
)a group by 日期,姓名')--删除测试临时表
drop table #t
create table 消费数据(FDateTime datetime,Name varchar(10),[Money] decimal(10,1),Mach char(2))
insert 消费数据
select '2004-01-01 12:10:15','张三',4.5,'01'
union all select '2004-01-01 17:10:15','张三',6.8,'01'
union all select '2004-01-01 18:10:15','张三',10,'01'
union all select '2004-01-01 12:10:15','李四',12,'02'
go--生成临时表
select id=identity(int,1,1),* into #t from 消费数据--查询
declare @s varchar(8000),@i int
select @s='',@i=max(ii)
from(
select ii=count(*) from 消费数据 group by name) a
while @i>0
select @s=',['+right('0'+cast(@i as varchar),2)+']=sum(case dd when '+cast(@i as varchar)+' then [money] else 0 end)'+@s
,@i=@i-1
exec('select 日期,姓名'+@s+' from(
select 日期=convert(varchar(10),FDateTime,120),姓名=name
,[money],dd=(select sum(1) from #t where name=a.name and id<=a.id)
from #t a
)a group by 日期,姓名')--删除测试临时表
drop table #t
go--删除测试
drop table 消费数据/*--测试结果日期 姓名 01 02 03
---------- ---------- ----------- ---------- ---------
2004-01-01 李四 12.0 .0 .0
2004-01-01 张三 4.5 6.8 10.0
--*/
declare @s varchar(8000),@i int
select @s='',@i=max(ii)
from(
select ii=count(*) from 消费数据 group by name) a
while @i>0
select @s=',['+right('0'+cast(@i as varchar),2)+']=sum(case dd when '+cast(@i as varchar)+' then [money] else 0 end)'+@s
,@i=@i-1
exec('select 日期,姓名'+@s+' from(
select 日期=convert(varchar(10),FDateTime,120),姓名=name
,[money],dd=(select sum(1) from 消费数据 where name=a.name and id<=a.id)
from 消费数据 a
)a group by 日期,姓名')
create table 消费数据(id int identity(1,1),FDateTime datetime,Name varchar(10),[Money] decimal(10,1),Mach char(2))
insert 消费数据
select '2004-01-01 12:10:15','张三',4.5,'01'
union all select '2004-01-01 17:10:15','张三',6.8,'01'
union all select '2004-01-01 18:10:15','张三',10,'01'
union all select '2004-01-01 12:10:15','李四',12,'02'
go--查询
declare @s varchar(8000),@i int
select @s='',@i=max(ii)
from(
select ii=count(*) from 消费数据 group by name) a
while @i>0
select @s=',['+right('0'+cast(@i as varchar),2)+']=sum(case dd when '+cast(@i as varchar)+' then [money] else 0 end)'+@s
,@i=@i-1
exec('select 日期,姓名'+@s+' from(
select 日期=convert(varchar(10),FDateTime,120),姓名=name
,[money],dd=(select sum(1) from 消费数据 where name=a.name and id<=a.id)
from 消费数据 a
)a group by 日期,姓名')
go--删除测试
drop table 消费数据/*--测试结果日期 姓名 01 02 03
---------- ---------- ----------- ---------- ---------
2004-01-01 李四 12.0 .0 .0
2004-01-01 张三 4.5 6.8 10.0
--*/