关闭

拼接多条记录的某个字段

1571人阅读 评论(0) 收藏 举报

有时候我们会遇到一条记录对应多条数据,需要拼接显示数据中的某个字段,例如项目实体表中有一条项目,对应设总表中的对条设总数据,这个时候我们需要将项目名称,设总名称查询出来显示在一个gridview列表中,就需要用sql拼接数据。

代码如下:

dtAllDate = new DataTable();
                    string sqlGetMen = string.Format(@"CREATE FUNCTION AllDesginMaster(@organid uniqueidentifier)
                            RETURNS VARCHAR(280)
                            AS
                            BEGIN
                            DECLARE @designMasters varchar(280)
                            SET  @designMasters=''       -----必须初始化
                            SELECT @designMasters+=coalesce(CASE @designMasters WHEN '' THEN '' END ,',' )+CAST(DesignName AS VARCHAR) 
                            FROM ProjectPhaseDesignMaster 
                            WHERE OrganizationId=@organid
                            RETURN @designMasters
                            END
                            ");
                    string sqlGetAllDate=string.Format(@"
                            SELECT P.OrganizationId,P.ProjectPhaseCode,P.ProjectPhaseName,P.ParamProjectTypeID,P.ProjectTypeCode,P.ProjectTypeName,DBO.AllDesginMaster(P.OrganizationId) AS DesginMasters,
                            P.PhaseID,P.PhaseName,P.DesignSize,
                            ( CASE  P.ProjectState WHEN 1 THEN '已启动'
                                        WHEN 2 THEN '正在进行'
                                        WHEN 3 THEN '暂停'
                                        WHEN 4 THEN '终止'
                                        WHEN 5 THEN '设计完成'
                                        WHEN 6 THEN '结束' END ) AS ProjectState
                            ,P.FormProjectDate,P.InstitutionFinishDate,ISNULL(C.ContractSum,0)/10000 AS ContractSum ,
                            ISNULL(C.CompleteReceiveSum,0)/10000 AS CompleteReceiveSum,(ISNULL(C.ContractSum,0)-ISNULL(C.CompleteReceiveSum,0))/10000 AS ResidueSum,
                            ISNULL(P.SuverySchedule,0) AS SuverySchedule,ISNULL(P.FinishRate,0) AS FinishRate,M.CapabilityID
                            FROM ProjectPhaseEntity P LEFT JOIN ContractEntity C
                            ON P.OrganizationId=C.ProjectID  LEFT JOIN MarketProject M 
                            ON P.MarketProjectID=M.MarketProjectID
                            ORDER BY P.ParamProjectTypeID,P.ProjectPhaseCode
                            DROP FUNCTION DBO.AllDesginMaster");
                    KPMSDB.ExecuteNonQuery(CommandType.Text, sqlGetMen);
                    dtAllDate = KPMSDB.ExecuteDataSet(CommandType.Text, sqlGetAllDate).Tables[0];


说明:在sql查询器中,sqlGetMen和sqlGetAllDate通过“GO”连接可以一起执行,但是在asp.net中是不识别GO,会报GO附近有语法错误,所以我给拆成两条sql语句分别执行。

效果显示如下:

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:207498次
    • 积分:2334
    • 等级:
    • 排名:第16602名
    • 原创:53篇
    • 转载:43篇
    • 译文:0篇
    • 评论:7条
    文章分类
    最新评论