拼接多条记录的某个字段

有时候我们会遇到一条记录对应多条数据,需要拼接显示数据中的某个字段,例如项目实体表中有一条项目,对应设总表中的对条设总数据,这个时候我们需要将项目名称,设总名称查询出来显示在一个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语句分别执行。

效果显示如下:

阅读更多
个人分类: sql
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭