34,576
社区成员
发帖
与我相关
我的任务
分享
use Tempdb
go
--> -->
if not object_id(N'Forbug_HTMHkZje') is null
drop table Forbug_HTMHkZje
Go
Create table Forbug_HTMHkZje([id] int,[deptcode] NVARCHAR(20),[time] Datetime,[money] int)
Insert Forbug_HTMHkZje
select 1,'001014','2011-04-01 00:00:00:000',1000000 union all
select 2,'001014','2011-05-01 00:00:00:000',5000000 union all
select 3,'001013','2011-05-01 00:00:00:000',3000000 union all
select 4,'001013','2011-04-01 00:00:00:000',2000000 union all
select 5,'001015','2011-06-01 00:00:00:000',350000 union all
select 6,'001016','2011-04-01 00:00:00:000',25000000
Go
DECLARE @s NVARCHAR(2000)
SET @s='select convert(varchar(10),[time],120) as Date'
Select @s=@s+','+QUOTENAME(deptcode)+'=sum(case when deptcode='+QUOTENAME(deptcode,'''')+' then [money] else 0 end)' from Forbug_HTMHkZje GROUP BY deptcode
EXEC(@s+' from Forbug_HTMHkZje group by convert(varchar(10),[time],120)')
/*
Date 001013 001014 001015 001016
2011-04-01 2000000 1000000 0 25000000
2011-05-01 3000000 5000000 0 0
2011-06-01 0 0 350000 0
*/
declare @sql varchar(8000)
set @sql = 'select time'
select @sql = @sql + ', max(case deptcode when ''' + deptcode +
''' then [money] else 0 end) [' + deptcode + ']'
from (select distinct deptcode from Forbug_HTMHkZje) as a
set @sql = @sql + ' from Forbug_HTMHkZje group by time'
exec(@sql)
declare @sql varchar(8000)
set @sql = 'select time'
select @sql = @sql + ', max(case deptcode when ''' + deptcode +
''' then money else 0 end) [' + deptcode + ']'
from (select distinct deptcode from Forbug_HTMHkZje) as a
set @sql = @sql + ' from Forbug_HTMHkZje group by time'
exec(@sql)