行列转置

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[FeeBudgetYear_RowColumnChang]
@Curryear varchar(4),@DeptCode varchar(6)
	
AS
declare @SQL varchar(8000)
	,@FieldFZ VARCHAR(50) --分组字段
	,@FieldJS varchar(50) --计算字段
	,@TMPsql varchar(8000)--临时字符串
   

set @FieldFZ='CurrMonth'
set @FieldJS='BudgetMoney'
set @TMPsql=''

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


select @TMPsql=@TMPsql+',['+a.CurrMonth+']=sum(case  '+@FieldFZ+' when '+a.CurrMonth+' then '+@FieldJS+' else 0 end)'
from (
select '1' as CurrMonth
union 
select '2' as CurrMonth
UNION
select '3' as CurrMonth
union
select '4' as CurrMonth
union 
select '5' as CurrMonth
union
select '6' as CurrMonth
union
select '7' as CurrMonth
union
select '8' as CurrMonth
union
select '9' as CurrMonth
union
select '10' as CurrMonth
union
select '11' as CurrMonth
union
select '12' as CurrMonth
) a 


--print @TMPsql
--DECLARE  @Curryear int
--         ,@DeptID VARCHAR(6)
--
--SET @Curryear=@year
--set @DeptID=@DeptCode
set @SQL='
select b.ItemName,a.feeid'+@TMPsql+'
from dbo.FeeBudgetYear a,FeeItems b where a.FeeID=b.FeeID and a.CurrYear='''+@Curryear+''' and a.DeptCode='''+@DeptCode+''' 
group by a.feeid,b.ItemName'

--print 
 

exec (@SQL)
END
 

  select b.ItemName,a.feeid,[1]=sum(case  CurrMonth when 1 then BudgetMoney else 0 end),[10]=sum(case  CurrMonth when 10 then BudgetMoney else 0 end),[11]=sum(case  CurrMonth when 11 then BudgetMoney else 0 end),[12]=sum(case  CurrMonth when 12 then BudgetMoney else 0 end),[2]=sum(case  CurrMonth when 2 then BudgetMoney else 0 end),[3]=sum(case  CurrMonth when 3 then BudgetMoney else 0 end),[4]=sum(case  CurrMonth when 4 then BudgetMoney else 0 end),[5]=sum(case  CurrMonth when 5 then BudgetMoney else 0 end),[6]=sum(case  CurrMonth when 6 then BudgetMoney else 0 end),[7]=sum(case  CurrMonth when 7 then BudgetMoney else 0 end),[8]=sum(case  CurrMonth when 8 then BudgetMoney else 0 end),[9]=sum(case  CurrMonth when 9 then BudgetMoney else 0 end)  from dbo.FeeBudgetYear a,FeeItems b where a.FeeID=b.FeeID and a.CurrYear='2009' and a.DeptCode='BJ001'   group by a.feeid,b.ItemName,b.ID