数据库中纵表转换为横表的存储过程

 

1.       子表-----计划收入

select CollectionPlanID as 收入编号,CollectionTime as 计划收入时间,collectionMoney 计划收入金额,contractid 合同编号

from T_Bas_CollectionPlan

 

2.子表-----实际收入

select collectionid as 收入编号,collectiontime as 实际收入时间,collectionMoney 实际收入金额,contractid 合同编号

from T_Bas_Collection

3.合并后的纵表--------收入纵表

SELECT ContractID 合同编号, '计划收入' as 收入类型, SUM(CollectionMoney) AS 收入金额,year(CollectionTime) as 年份,month(CollectionTime) as 月份

FROM dbo.T_Bas_CollectionPlan

GROUP BY ContractID,year(CollectionTime),month(CollectionTime)

union

SELECT ContractID AS 合同编号, '实际收入' as 收入类型,SUM(CollectionMoney) AS 收入金额, year(CollectionTime) as 年份,month(CollectionTime) as 月份

FROM dbo.T_Bas_Collection

GROUP BY ContractID,year(CollectionTime),month(CollectionTime)

4.转换后的横表---------收入横表

USE contract

GO

/****** 对象: StoredProcedure [dbo].[GetContractBudget]    脚本日期: 05/14/2008 15:55:51 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:    <Author,,Name>

-- Create date: <Create Date,,>

-- Description:   <Description,,>

-- =============================================

CREATE PROCEDURE [dbo].[GetContractBudget]

    AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for procedure here

    --存放收入数据包括：计划收入和实际收入的纵表格式

    create table #tmp1(contractid int,acollection nvarchar(50),collectionMoney decimal(16,2) ,ayear int , amonth int)

    insert into #tmp1(contractid,acollection,collectionMoney,ayear,amonth)

       SELECT ContractID, '计划收入' as acollection, SUM(CollectionMoney) AS CollectionPlan,

       year(CollectionTime) as collectionPlanYear,

       month(CollectionTime) as collectionPlanMonth

       FROM dbo.T_Bas_CollectionPlan

       GROUP BY ContractID,year(CollectionTime),month(CollectionTime)

       union

       SELECT ContractID AS ContractNo, '实际收入' as acollection,SUM(CollectionMoney) AS sumCollection,

       year(CollectionTime) as CollectionRealYear,

       month(CollectionTime) as CollectionRealMonth

       FROM dbo.T_Bas_Collection

       GROUP BY ContractID,year(CollectionTime),month(CollectionTime)

    --select * from #tmp1

    --存放--存放收入数据包括：计划收入和实际收入的横表格式

    create table #result1(contractID int,ayear int,amonth int,collectionPlan decimal(16,2),collectionReal decimal(16,2))

    insert into #result1

       select contractid,ayear,amonth, sum(case acollection when '计划收入' then collectionMoney else 0 end) as 'a计划收入',

       sum(case acollection when '实际收入' then collectionMoney else 0 end) as 'a实际收入'

       from #tmp1

       group by contractid,ayear,amonth

   

    select contractid 合同编号,ayear 年份,amonth 月份,collectionPlan 计划收入金额,collectionReal 实际收入金额from #result1

   

    drop table #tmp1

    drop table #result1

END