MSSql表列拼接成字符串简析

2012-5-22 10:17 715 0

摘要: 将表列拼接成字符串。闲话不多说切入正题，看代码： [sql] select STUFF( REPL...

关键词： nbsp select REPLACE datadictionary Expenditure 2119416553 fieldCode fieldName fieldType memberid

将表列拼接成字符串。闲话不多说切入正题，看代码： [sql] select STUFF( REPLACE( www.2cto.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.2cto.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.2cto.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

声明：文章版权归原作者所有部分文章转自互联网如有侵权请联系 [邮箱地址] 删除