try
CREATE procedure he_o;1
as
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when pathid='''+pathid+''' then Rtrim(amount) else '''' end) as ['+pathid+']'
from he_productorder_view group by pathid
exec('select sum(amount) as amount,breed'+ @sql+' from he_productorder_view group by breed,pid order by pid asc')
GO
CREATE procedure he_o;1
as
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when pathid='''+pathid+''' then Rtrim(amount) else '''' end) as ['+pathid+']'
from he_productorder_view group by pathid
exec('select sum(amount) as amount,breed'+ @sql+' from he_productorder_view group by breed,pid order by pid asc')
GO
--加上and amount>0
CREATE procedure he_o;1
as
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case when pathid='''+pathid+''' and amount>0 then amount else '''' end) as ['+pathid+']'
from he_productorder_view group by pathid
exec('select sum(amount) as amount,breed'+ @sql+' from he_productorder_view group by breed,pid order by pid asc')
GO
我将sum改成max,再在amount前面加rtrim就不会有0,但是没有对pathid求和,只得到最大值,
您还能帮我想想吗?我一定要求和的且不能显示0,一定给分,谢谢!
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',case when amount>0 then sum(case when pathid='''+pathid+''' then amount else '''' end) else '''' end as ['+pathid+']'
from he_productorder_view group by pathid
print('select sum(amount) as amount,breed'+ @sql+' from he_productorder_view group by breed,pid order by pid asc')
列 'he_productorder_view.Amount' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
CREATE procedure he_o;1
as
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',isnull(cast(sum(case when pathid='''+pathid+''' then amount end) as char(5)),'''') as ['+pathid+']'
from he_productorder_view group by pathid
exec('select sum(amount) as amount,breed'+ @sql+' from he_productorder_view group by breed,pid order by pid asc')
GO