通过修改存储过程获取汇总表(pivot)

        /// <summary>
        /// 修改存储过程并重新执行得到汇总表
        /// </summary>
        public void ModifyProcOfPivot_Summary()
        {
            string sqlPart = @"

                        {0} PROCEDURE dbo.GeneratePivot as

                        WITH step1 --基本转换
                        AS (
                        SELECT t.OBJECT_ID,CPLX,FKFS
	                        ,t.YJD,t.DFDQ 
                        FROM dbo.ZDMX t 
                        )
                        AS (
                        SELECT step1.*
	                        ,CASE 
		                        WHEN step1.FKFS='JF' THEN ISNULL(jf.FGS,'--')
		                        WHEN step1.FKFS='DF' THEN ISNULL(df.FGS,'--')
		                        ELSE '---'
	                         END fgs	
		 
                        FROM step1 LEFT JOIN dbo.DQFGSDZB jf ON step1.YJD = jf.DQ
		                        LEFT JOIN dbo.DQFGSDZB df ON step1.DFDQ = df.dq
                        )
                        ,step3 AS (
                        SELECT a.fgs,a.CPLX,SUM(CAST(b.FYY AS DECIMAL(20,2))) fy 
                        FROM step2 a JOIN dbo.ZDMX b ON a.OBJECT_ID=b.OBJECT_ID
                        GROUP BY a.fgs,a.CPLX
                        )
                        ,step4 AS (
                        SELECT tbl.fgs, {1} 
                        FROM step3
                        PIVOT(
	                        SUM(fy) FOR CPLX IN ({1})
                        ) tbl
                        )
                        SELECT ''''+b.YJZH ,b.mc ,b.CBZXDM ,b.FGS,a.fgs ,{1} 
                        FROM step4 a LEFT JOIN dbo.FGSDZB b ON a.fgs=b.JC
                        order by b.xh

            ";

            DataTable dtExist = MyCommon.GetDt("SELECT 1 c FROM jrtl.sys.objects WHERE object_id=  OBJECT_ID('dbo.GeneratePivot') AND type='P'", sqc.ConnectString);
            string opr = (dtExist.Rows.Count == 0) ? "Create" : "Alter";
            string strFields = GetPivotFields();
            string sql = string.Format(sqlPart,opr,strFields);
            MyCommon.ExecuteSql(sql, sqc.ConnectString);

            StoreProcedure proc = new StoreProcedure(sqc.ConnectString, "dbo.GeneratePivot");
            DataTable dtSummary = proc.ExecuteDataTable();
            Excel.Application app = new Excel.Application(); app.Visible = true;
            MyCommon.FillDtToWorksheet(app, dtSummary);
            
        }

        /// <summary>
        /// 获取pivot的字段列表字符串
        /// </summary>
        /// <returns></returns>
        private string GetPivotFields()
        {
            string sql = @"
                        SELECT ISNULL(a.mc,b.cplx) cplx,ISNULL(a.xh,15) xh 
                        FROM (
	                        SELECT mc,xh FROM dbo.CPLX ) a FULL JOIN (
	                        SELECT DISTINCT 
		                          CASE t.CPLX 
				                        WHEN 'xx' THEN 'xx'	
				                        WHEN 'xx' THEN 'xx' 
				                        ELSE T.CPLX 
			                        END CPLX
	                        FROM dbo.ZDMX t 
	                        ) b ON a.mc=b.CPLX

            ";
            DataTable dtFields = MyCommon.GetDt(sql, sqc.ConnectString);
            List<string> ls = new List<string>();
            for (int i = 0; i < dtFields.Rows.Count; i++)
            {
                ls.Add(string.Format("[{0}]", dtFields.Rows[i][0].ToString()));
            }
            return string.Join(",", ls.ToArray());
        }