用存储过程实现 思路是这样的 1.先将表中要横向显示的字段用Distinct查询出来放在临时表中 2.查询临时表拼接横向列头 3.生成最后结果集 具体方法可参照以下代码: CREATE proc Analyse_Pro_Finance_SettleIn_Cash ... as declare @sqlcol varchar(8000) set @sqlcol=''--汇总列头 declare @colname varchar(8000) set @colname=''--最终显示列头--查询该日期内所涉及的费用项 横向展示(行转列) select distinct bof.bof_name into #feeitem from Finance_settlein_info fsi with(nolock) where ... --生成费用查询列 select @sqlcol=@sqlcol+',case max(bof_name) when '''+bof_name+''' then sum(feemoney) else ''0'' end as '''+bof_name+'|N''' ,@colname=@colname+',max(['+bof_name+'|N]) as ['+bof_name+'|N]' from #feeitem drop table #feeitem --生成结果集 exec( ' select tid,jobcode as [收款单号],sia_unittid as [客户代码],UnitName as [客户名称],tdcode as [提单号],storetype as [仓储类型] ,[库位]=s_name,xx20 as ''20'',xx40 as ''40'''+@colname+' from ( select tid,jobcode,sia_unittid,UnitName,tdcode,sia_type,storetype,s_name,xx20,xx40,bof_tid'+@sqlcol+' from #unit group by #unit.tid,#unit.jobcode,#unit.sia_unittid,#unit.UnitName,tdcode,sia_type,storetype,s_name,xx20,xx40,bof_tid ) a group by tid,jobcode,sia_unittid,UnitName,tdcode,sia_type,storetype,s_name,xx20,xx40 ')drop table #unitGO 最后拼接出exec里面的语句 使用两个变量@sqlcol,@colname 用于分组排除重复项 希望对你有所帮助
http://blog.csdn.net/sz_bdqn/article/details/5825415
希望对你有用
思路是这样的
1.先将表中要横向显示的字段用Distinct查询出来放在临时表中
2.查询临时表拼接横向列头
3.生成最后结果集
具体方法可参照以下代码:
CREATE proc Analyse_Pro_Finance_SettleIn_Cash
...
as
declare @sqlcol varchar(8000) set @sqlcol=''--汇总列头
declare @colname varchar(8000) set @colname=''--最终显示列头--查询该日期内所涉及的费用项 横向展示(行转列)
select distinct bof.bof_name into #feeitem
from Finance_settlein_info fsi with(nolock)
where ...
--生成费用查询列
select @sqlcol=@sqlcol+',case max(bof_name) when '''+bof_name+''' then sum(feemoney) else ''0'' end as '''+bof_name+'|N'''
,@colname=@colname+',max(['+bof_name+'|N]) as ['+bof_name+'|N]'
from #feeitem
drop table #feeitem
--生成结果集
exec(
'
select tid,jobcode as [收款单号],sia_unittid as [客户代码],UnitName as [客户名称],tdcode as [提单号],storetype as [仓储类型]
,[库位]=s_name,xx20 as ''20'',xx40 as ''40'''+@colname+'
from
( select tid,jobcode,sia_unittid,UnitName,tdcode,sia_type,storetype,s_name,xx20,xx40,bof_tid'+@sqlcol+'
from #unit
group by #unit.tid,#unit.jobcode,#unit.sia_unittid,#unit.UnitName,tdcode,sia_type,storetype,s_name,xx20,xx40,bof_tid
) a
group by tid,jobcode,sia_unittid,UnitName,tdcode,sia_type,storetype,s_name,xx20,xx40
')drop table #unitGO
最后拼接出exec里面的语句 使用两个变量@sqlcol,@colname 用于分组排除重复项
希望对你有所帮助