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
