SQL 存储过程实现行转列、手动增加列、列的个数由不同的行数决定

<p>alter procedure get_purchase_goodsinfo_all(@supplierid int)
as
begin
declare @sql varchar(max)
select @sql = 'select ROW_NUMBER()over(order by 物品编号) 行号,a.*,b.合计数量,b.平均单价,(b.平均单价*b.合计数量) as 合计金额 from ('
select @sql = @sql + 'select distinct b.紫色,b.红色,''正常'' 紧急状态,a.*,b.单位 from ('
--select @sql = 'select distinct a.* from ('
set @sql = @sql + 'select goodsNo as 物品编号,cnname as 物品名称'
--select @sql = @sql +',max(case contractNo when ''' +contractNo+''' then CAST((realinqty - thispurchaseqty) as varchar(20)) else '''' end) ['+contractNo+']'
select @sql = @sql +',max(case contractNo when ''' +contractNo+''' then ((case  when cast((realInqty-thispurchaseqty) as varchar(20))<>''0.0000'' then cast((realInqty-thispurchaseqty) AS varchar(20)) when cast((realInqty-thispurchaseqty) as varchar(20))=''0.0000'' then '''' end)) end) ['+contractNo+']'
from (select distinct contractNo from (select 
(select contractNo from dbo.purchase_contract where id = a.mainid ) contractNo,
goodsNo,
id,
cnname
from purchase_contract_goods a where mainid in
(
select id from dbo.purchase_contract 
where (type=1 or type=2 or type=11 or type=12 or type=13 or type=14) and   status=8 and instockstatus<4 and  supplierid = @supplierid
))x) as a
set @sql = @sql +'from (select contractNo,goodsNo,id,
cnname, 
(inqty-backqty) as realInqty,
swareqty,
thispurchaseqty,
backqty
from(select 
(select contractNo from dbo.purchase_contract where id = a.mainid ) contractNo,
goodsNo,
id,
cnname, 
case when goodstype=1 then
isnull((select isnull(balanceqty,0) from dbo.bas_goods_center where warehouseid=2 and style=1 and goodsid=a.goodsid),0)
when goodstype=2 then
isnull((select isnull(balanceqty,0) from dbo.M_property_warehouse where warehouseid=1 and style=1 and wid=a.goodsid),0)
end  as swareqty,
thispurchaseqty, 
isnull((select SUM(m.instockqty) from dbo.wm_stock_goods m where m.purchasegoodsid =a.id and instocktype<17),0) as inqty, 
backqty,
isnull((case when goodstype=1 then (select isnull(safeqty,0) from dbo.bas_goods where id=a.goodsid)  
when goodstype=2 then  (select isnull(savesafeqty,0) 
from dbo.M_property where id=a.goodsid) end ),0) as safeqty  
from purchase_contract_goods a where mainid in
(
select id from dbo.purchase_contract 
where (type=1 or type=2 or type=11 or type=12 or type=13 or type=14) and   status=8 and instockstatus<4 and  supplierid = '+cast(@supplierid as varchar(50))+'
))j)z group by goodsNo,cnname) a 
inner join 
(select 
goodsNo,
((case when goodstype=1 then
isnull((select isnull(balanceqty,0) from bas_goods_center where warehouseid=2 and style=1 and goodsid=a.goodsid),0)
when goodstype=2 then
isnull((select isnull(balanceqty,0) from M_property_warehouse where warehouseid=1 and style=1 and wid=a.goodsid),0)
end)-(isnull((case when goodstype=1 then (select isnull(savesafeqty,0) from bas_goods where id=a.goodsid)  
when goodstype=2 then  (select isnull(savesafeqty,0) from M_property where id=a.goodsid) end ),0))) 紫色,
((case when goodstype=1 then
isnull((select isnull(balanceqty,0) from bas_goods_center where warehouseid=2 and style=1 and goodsid=a.goodsid),0)
when goodstype=2 then
isnull((select isnull(balanceqty,0) from M_property_warehouse where warehouseid=1 and style=1 and wid=a.goodsid),0)
end)-(case when goodstype=2 then isnull(dbo.Get_material_needqty(a.goodsid,1,1),0) 
when goodstype=1 then isnull(dbo.Get_goods_needqty(a.goodsid,2),0) end )) as 红色,
--purchasePrice as 单价,
(select cnname from bas_qtyunit where id=qtyunit) as 单位
from purchase_contract_goods a where mainid in
(
select id from dbo.purchase_contract 
where (type=1 or type=2 or type=11 or type=12 or type=13 or type=14) and   status=8 and instockstatus<4 and  supplierid = '+cast(@supplierid as varchar(50))+'
) ) b  on a.物品编号 = b.goodsNo) a inner join 
(select goodsNo,sum(realInqty-thispurchaseqty) 合计数量,AVG(purchasePrice) 平均单价 from purchase_contract_goods a where --goodsid = 10186 
mainid in (
select id from dbo.purchase_contract 
where (type=1 or type=2 or type=11 or type=12 or type=13 or type=14) and   status=8 and instockstatus<4 and  supplierid = '+cast(@supplierid as varchar(50))+'
)
group by goodsNo) b on b.goodsNo = a.物品编号  '
exec (@sql)
end</p><p>exec get_purchase_goodsinfo_all 177</p><p> </p>