Create Table T( ID char(10), Amount char(20), DateTime char(20)) insert into T select '1001','20','2005-05-01' union all select '1002','10','2005-05-01' union all select '1001','30','2005-05-02' -------------實現語句------------------------ select id,[5月1日]=(select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-01' ) , [5月2日]=isnull((select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-02' ),'') from T a group by Id --------------結果--------------------- 1001 20 30 1002 10
-------上面的例子,如果要31都出來,則要把列全補齊全 select id,[5月1日]=(select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-01' ) , [5月2日]=isnull((select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-02' ),''), [5月3日]=isnull((select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-03' ),''), ...... from T a group by Id
declare @s varchar(8000) set @s = ''select @s = @s + ','+DateTime+'=sum(case when DateTime = '''+DateTime+''' then cast(Amount as numeric(8,2)) else 0.0 end)' from t group by DateTime set @s = 'select 编码 = ID '+@s+',合计 = sum(cast(Amount as numeric(8,2))) from t where left(DateTime,1) = ''5'' group by ID with rollup'exec(@s)
Create Table T( ID char(10), Amount char(20), DateTime char(20))Declare @str varchar(1000) set @str='' select @str=@str+',['+cast(month(DateTime) as char(2))+'月'+cast(day(DateTime) as char(2))+'日]=sum(case DateTime when '''+DateTime+''' then Amount else 0 end)' from T Group by DateTime Print(@str) exec('select id'+@str+' from T Group by id')
to Night_Elf(风暴虫) 没有求和啊
UP 先!~:http://community.csdn.net/Expert/topic/3799/3799795.xml?temp=.2657892
Declare @str varchar(1000) set @str='' select @str=@str+',['+cast(month(DateTime) as char(2))+'月'+cast(day(DateTime) as char(2))+'日]=sum(case DateTime when '''+DateTime+''' then Amount else 0 end)' from T Group by DateTimeselect @str=@str+',[合计]=sum( Amount )' Print('select id'+@str+' from T Group by id') exec('select id'+@str+' from T Group by id')
-----少了一個求和了----- Create Table T( ID char(10), Amount char(20), DateTime char(20)) insert into T select '1001','20','2005-05-01' union all select '1002','10','2005-05-01' union all select '1001','30','2005-05-02' -------------實現語句------------------------ select id,[5月1日]=(select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-01' ) , [5月2日]=isnull((select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-02' ),''),和=sum(cast(amount as decimal(9,2))) from T a group by Id --------------結果--------------------- 1001 20 30 1002 10
-----少了一個求和了----- Create Table T( ID char(10), Amount char(20), DateTime char(20)) insert into T select '1001','20','2005-05-01' union all select '1002','10','2005-05-01' union all select '1001','30','2005-05-02' -------------實現語句------------------------ select id,[5月1日]=(select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-01' ) , [5月2日]=isnull((select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-02' ),''),和=sum(cast(amount as decimal(9,2))) from T a group by Id --------------結果--------------------- 1001 20 30 50.00 1002 10 10.00
-------上面的例子,如果要31都出來,則要把列全補齊全 select id,[5月1日]=(select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-01' ) , [5月2日]=isnull((select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-02' ),''), [5月3日]=isnull((select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-03' ),''), ......和=sum(cast(amount as decimal(9,2))) from T a group by Id
create table tt (AgentDepartID char(10) , -- 营业厅编码 StaffID char(10) , -- 营业员工号 BankSerialNumber char(20) , -- 银行流水号 TransferAmount char(20) , --交易金额(注意类型) TransferDateTime char(20) , --交易时间 CancelFlag char(2) --返销标志(0—正常,9—返销) ) -----------------------测试数据,有可能类型 declare @s int set @s=6 select AgentDepartID,TransferAmount, cast(cast(datepart(yyyy,TransferDateTime) as varchar(4))+'-'+cast(datepart(mm,TransferDateTime) as varchar(2))+'-'+cast(datepart(dd,TransferDateTime) as varchar(2)) as datetime) TransferDateTime,CancelFlag into #tb from tb where datepart(dd,TransferDateTime)=@sdeclare @sql varchar(8000),@s1 varchar(8000) select @sql = '',@s1='' select @sql =@sql+ ',['+cast(datepart(dd,TransferDateTime) as varchar(2))+'月'+cast(datepart(dd,TransferDateTime) as varchar(2))+'日'+']=sum(case datepart(dd,TransferDateTime) when '+cast(datepart(dd,TransferDateTime)as varchar(2))+' then cast(ltrim(rtrim(TransferAmount)) as decimal(20,2)) else 0 end)' ,@s1=@s1+',sum(case datepart(dd,TransferDateTime) when '+cast(datepart(dd,TransferDateTime)as varchar(2))+' then cast(ltrim(rtrim(TransferAmount)) as decimal(20,2)) else 0 end)/sum(case datepart(dd,TransferDateTime) when '+cast(datepart(dd,TransferDateTime)as varchar(2))+' then 1 else 0 end)' from #tb group by TransferDateTime print @sql exec('select 编码=case grouping(AgentDepartID) when 1 then ''合计'' else AgentDepartID end'+@sql+',合计=sum(cast(ltrim(rtrim(TransferAmount)) as decimal(20,2)) ) from #tb group by AgentDepartID with rollup')drop table #tb
是2004-12-12 型
ID char(10),
Amount char(20),
DateTime char(20))
insert into T
select '1001','20','2005-05-01' union all
select '1002','10','2005-05-01' union all
select '1001','30','2005-05-02'
-------------實現語句------------------------
select id,[5月1日]=(select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-01' ) ,
[5月2日]=isnull((select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-02' ),'')
from T a group by Id
--------------結果---------------------
1001 20 30
1002 10
select id,[5月1日]=(select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-01' ) ,
[5月2日]=isnull((select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-02' ),''),
[5月3日]=isnull((select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-03' ),''),
......
from T a group by Id
set @s = ''select
@s = @s +
','+DateTime+'=sum(case when DateTime = '''+DateTime+''' then cast(Amount as numeric(8,2)) else 0.0 end)'
from t
group by DateTime
set @s = 'select 编码 = ID '+@s+',合计 = sum(cast(Amount as numeric(8,2))) from t where left(DateTime,1) = ''5'' group by ID with rollup'exec(@s)
ID char(10),
Amount char(20),
DateTime char(20))Declare @str varchar(1000)
set @str=''
select @str=@str+',['+cast(month(DateTime) as char(2))+'月'+cast(day(DateTime) as char(2))+'日]=sum(case DateTime when '''+DateTime+''' then Amount else 0 end)' from T Group by DateTime
Print(@str)
exec('select id'+@str+' from T Group by id')
set @str=''
select @str=@str+',['+cast(month(DateTime) as char(2))+'月'+cast(day(DateTime) as char(2))+'日]=sum(case DateTime when '''+DateTime+''' then Amount else 0 end)' from T Group by DateTimeselect @str=@str+',[合计]=sum( Amount )'
Print('select id'+@str+' from T Group by id')
exec('select id'+@str+' from T Group by id')
Create Table T(
ID char(10),
Amount char(20),
DateTime char(20))
insert into T
select '1001','20','2005-05-01' union all
select '1002','10','2005-05-01' union all
select '1001','30','2005-05-02'
-------------實現語句------------------------
select id,[5月1日]=(select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-01' ) ,
[5月2日]=isnull((select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-02' ),''),和=sum(cast(amount as decimal(9,2)))
from T a group by Id
--------------結果---------------------
1001 20 30
1002 10
Create Table T(
ID char(10),
Amount char(20),
DateTime char(20))
insert into T
select '1001','20','2005-05-01' union all
select '1002','10','2005-05-01' union all
select '1001','30','2005-05-02'
-------------實現語句------------------------
select id,[5月1日]=(select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-01' ) ,
[5月2日]=isnull((select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-02' ),''),和=sum(cast(amount as decimal(9,2)))
from T a group by Id
--------------結果---------------------
1001 20 30 50.00
1002 10 10.00
select id,[5月1日]=(select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-01' ) ,
[5月2日]=isnull((select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-02' ),''),
[5月3日]=isnull((select amount from T where id=a.id and right(convert(varchar(10),[datetime],120),5)='05-03' ),''),
......和=sum(cast(amount as decimal(9,2)))
from T a group by Id
(AgentDepartID char(10) , -- 营业厅编码
StaffID char(10) , -- 营业员工号
BankSerialNumber char(20) , -- 银行流水号
TransferAmount char(20) , --交易金额(注意类型)
TransferDateTime char(20) , --交易时间
CancelFlag char(2) --返销标志(0—正常,9—返销)
)
-----------------------测试数据,有可能类型
declare @s int
set @s=6
select AgentDepartID,TransferAmount,
cast(cast(datepart(yyyy,TransferDateTime) as varchar(4))+'-'+cast(datepart(mm,TransferDateTime) as varchar(2))+'-'+cast(datepart(dd,TransferDateTime) as varchar(2)) as datetime)
TransferDateTime,CancelFlag into #tb from tb where datepart(dd,TransferDateTime)=@sdeclare @sql varchar(8000),@s1 varchar(8000)
select @sql = '',@s1=''
select @sql =@sql+ ',['+cast(datepart(dd,TransferDateTime) as varchar(2))+'月'+cast(datepart(dd,TransferDateTime) as varchar(2))+'日'+']=sum(case datepart(dd,TransferDateTime) when '+cast(datepart(dd,TransferDateTime)as varchar(2))+' then cast(ltrim(rtrim(TransferAmount)) as decimal(20,2)) else 0 end)'
,@s1=@s1+',sum(case datepart(dd,TransferDateTime) when '+cast(datepart(dd,TransferDateTime)as varchar(2))+' then cast(ltrim(rtrim(TransferAmount)) as decimal(20,2)) else 0 end)/sum(case datepart(dd,TransferDateTime) when '+cast(datepart(dd,TransferDateTime)as varchar(2))+' then 1 else 0 end)'
from #tb
group by TransferDateTime
print @sql
exec('select 编码=case grouping(AgentDepartID) when 1 then ''合计'' else AgentDepartID end'+@sql+',合计=sum(cast(ltrim(rtrim(TransferAmount)) as decimal(20,2)) )
from #tb
group by AgentDepartID with rollup')drop table #tb