行列转置

最新推荐文章于 2025-08-07 14:50:49 发布

最新推荐文章于 2025-08-07 14:50:49 发布 · 119 阅读

文章标签：

#SQL #.net #Go

sql 专栏收录该内容

3 篇文章

订阅专栏

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