SQL交叉报表显示全为0,咋回事?

[qqf]

代码:
declare @sql varchar(8000)
set @sql = 'select kemu_id '
select @sql = @sql + ' , max(case ry when ''' + ry + ''' then ye else 0 end) [' + ry + ']'
from (select distinct ry from #tmpYe) as a
set @sql = @sql + ',sum(isnull(ye,0)) as Hj from #tmpYe group by kemu_id order by kemu_id'
exec(@sql)

数据集
kemu_id bm ry ye
1001 研发 虞良 -1046179.40
1001 研发 周健 -1158602.41
1002 DAS 虞良 -4485.00
1002 研发 测试1 -43714.00
1002 研发 虞良 -302317.35

交叉报表出来的结果
kemu_id 测试1 测试2 测试3 hj
1001 0.00 0.00 0.00 -2204781.81
1002 0.00 0.00 0.00 -350516.35

[百年树人]

楼主，你用max函数，是因为0大于负数，所以取0

[百年树人]

---测试数据---
if object_id('tempdb.dbo.#tmpye') is not null drop table #tmpye
go
create table #tmpye([kemu_id] int,[bm] varchar(4),[ry] varchar(5),[ye] numeric(9,2))
insert #tmpye
select 1001,'研发','虞良',-1046179.40 union all
select 1001,'研发','周健',-1158602.41 union all
select 1002,'DAS','虞良',-4485.00 union all
select 1002,'研发','测试1',-43714.00 union all
select 1002,'研发','虞良',-302317.35
 
---查询---
declare @sql varchar(8000)  
set @sql = 'select kemu_id '  
select @sql = @sql + ' , sum(case ry when ''' + ry + ''' then ye else 0 end) [' + ry + ']'  
from (select distinct ry from #tmpYe) as a  
set @sql = @sql + ',sum(isnull(ye,0)) as Hj from #tmpYe group by kemu_id order by kemu_id'  
exec(@sql)

---结果---
kemu_id     测试1                                     虞良                                      周健                                      Hj
----------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1001        0.00                                    -1046179.40                             -1158602.41                             -2204781.81
1002        -43714.00                               -306802.35                              0.00                                    -350516.35

[htl258_Tony]

declare @sql varchar(8000)
set @sql = 'select kemu_id '
select @sql = @sql + ' , sum(case ry when ''' + ry + ''' then ye else 0 end) [' + ry + ']'
from (select distinct ry from #tmpYe) as a
set @sql = @sql + ',sum(isnull(ye,0)) as Hj from #tmpYe group by kemu_id order by kemu_id'
exec(@sql)

是要这个效果吧

[qqf]

收到,谢谢.