SQL Server 自动交叉报表

帮公司做报表，要自动打横。warehouse 太多。用case 语句控制。

请看：　

REATE PROCEDURE StBrowseByPM
@PartNoFr  nvarchar(50) = 'CT0001-01-001',
@PartNoTo varchar(50)='CT0001-01-001'
AS
begin

select StPartNo,StWarehouse,sum(stStock) as TotalStock
Into #Temp_WhStock
from TblStock
where  (StPartNo  between @PartNoFr and @PartNoTo ) and (StStock >0)
group by StPartNo,StWarehouse
order by StPartNo,StWarehouse

--select * from #Temp_WhStock
declare @sql varchar(8000)
select @sql='select StPartNo'
select @sql=@sql+',['+stWarehouse+']=isnull(sum(case when stWarehouse='''+stWarehouse+''' then
[TotalStock] end),0)'
from #Temp_WhStock
group by stWarehouse
order by stWarehouse
select @sql=@sql+' from #Temp_WhStock group by StPartNo order by StPartNo'
--Print(@sql)
exec(@sql)
drop table #Temp_WhStock

就可以自动变成交叉报表。而且自动根据结果显示列数。