MSSql表列拼接成字符串简析

MSSql表列拼接成字符串简析

将表列拼接成字符串。闲话不多说切入正题，看代码：
[sql]  
select  STUFF(  
        REPLACE(    www.atcpu.com  
            REPLACE(  
                (select  fieldName as pc from cj_datadictionary as N  
                join [CSD_ORD_PayMent]  on costsid=fieldCode and fieldType='OP' where memberid=72157 and groupID='201205DD2119416553' and [Expenditure]>0  
                FOR XML AUTO),'<N pc="','+'  
            ),'"/>',''  
        ),1,1,''  
    )  

第一步：将表转化为xml格式

[sql]  
select  fieldName as pc from cj_datadictionary as N  
join [CSD_ORD_PayMent]  on costsid=fieldCode and fieldType='OP'  
where memberid=72157 and groupID='201205DD2119416553' and [Expenditure]>0  
FOR XML AUTO    www.atcpu.com  

第二步：替换<N pc=" 注：N是表名，pc是列名

[sql]  
select REPLACE((select  fieldName as pc from cj_datadictionary as N  
join [CSD_ORD_PayMent]  on costsid=fieldCode and fieldType='OP'  
where memberid=72157 and groupID='201205DD2119416553' and [Expenditure]>0  
FOR XML AUTO),'<N pc="','+')  

第三步：替换 "/> 注：每项都会以“<表 字段”开始，以“ "/>”结束

[sql]  
select REPLACE(REPLACE((select  fieldName as pc from cj_datadictionary as N  
join [CSD_ORD_PayMent]  on costsid=fieldCode and fieldType='OP'  
where memberid=72157 and groupID='201205DD2119416553' and [Expenditure]>0  
FOR XML AUTO),'<N pc="','+'),'"/>','')  

最后使用：STUFF() 函数作用：删除指定长度的字符，并在指定的起点上插入另外的字符串

[sql]  
select  STUFF(  
        REPLACE(    www.atcpu.com  
            REPLACE(  
                (select  fieldName as pc from cj_datadictionary as N  
                join [CSD_ORD_PayMent]  on costsid=fieldCode and fieldType='OP' where memberid=72157 and groupID='201205DD2119416553' and [Expenditure]>0  
                FOR XML AUTO),'<N pc="','+'  
            ),'"/>',''  
        ),1,1,''  
    )  


作者 zhengdjin