我根据这句话 select * from Forbug_HTMHkZje
得到的集合是
id deptcode time money
1 001014 2011-04-01 00:00:00:000 1000000
2 001014 2011-05-01 00:00:00:000 5000000
3 001013 2011-05-01 00:00:00:000 3000000
4 001013 2011-04-01 00:00:00:000 2000000
5 001015 2011-06-01 00:00:00:000 350000
6 001016 2011-04-01 00:00:00:000 25000000
我怎么写sql得到这样一个集合呢?
时间 001014 001013 001015 001016 --这一行标题,不用管,只用下面的集合
2011-04-01 1000000 2000000 0 25000000
2011-05-01 5000000 3000000 0 0
2011-06-01 0 0 350000 0
要得到这个集合,怎么写sql呢?高手帮忙看看啊。
得到的集合是
id deptcode time money
1 001014 2011-04-01 00:00:00:000 1000000
2 001014 2011-05-01 00:00:00:000 5000000
3 001013 2011-05-01 00:00:00:000 3000000
4 001013 2011-04-01 00:00:00:000 2000000
5 001015 2011-06-01 00:00:00:000 350000
6 001016 2011-04-01 00:00:00:000 25000000
我怎么写sql得到这样一个集合呢?
时间 001014 001013 001015 001016 --这一行标题,不用管,只用下面的集合
2011-04-01 1000000 2000000 0 25000000
2011-05-01 5000000 3000000 0 0
2011-06-01 0 0 350000 0
要得到这个集合,怎么写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)
列名 'money' 无效。
消息 207,级别 16,状态 1,第 1 行
列名 'money' 无效。
2楼的代码,,我用了报这个错啊?不知道什么原因?
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)
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
*/