sqlserver中怎么将一列数据拼接成一个字符串，并以换行符分开

‍

select W, X = (stuff((select '，' + X from table where W = a.W for xml path('')),1,1,'')) from table a group by W

-- 将W相同的X列拼接成一个字符串用逗号隔开

参考 http://blog.csdn.net/rolamao/article/details/7745972

什么数据库？

create table #(col1 int,col2 varchar(64))

go

insert into # select 1,'a`b`c`'

insert into # select 2,'d`e`f`'+char(13)+'g`h`l`'

insert into # select 3,'d`e`f`'+char(13)+'g`h`l`'+char(13)+'g`l`'

go

with cte1 as(

select col1,tmp from

(

select col1,xmlns = cast('<root><p>'+replace(col2,char(13),'</p><p>')+'</p></root>' as xml)

from #)p

cross apply

(

select tmp=c.v.value('.','nvarchar(100)')

from p.xmlns.nodes('/root/p')as c(v)

)s

)

select col1,

col2 = left(tmp,1),

col3 = left(stuff(tmp,1,2,''),1),

col4 = left(stuff(tmp,1,4,''),1)

from cte1

---------结果

col1col2 col3 col4

----------- ---- ---- ----

1 abc

2 def

2 ghl

3 def

3 ghl

3 gl

(6 行受影响)

dim s,v 'v用来保存用split分割后生成的字符串数组，即是你要的5条单独字符串

s="：--------------------------------------LVTDB12A77B001683LVVDA11B14D064217LVVDA11B54D062650LVVDA11B94D062652LVVDB12A24D065198------------------------------------"

s=replace(s,"-","")'去掉-

s=replace(s,"LV",",LV",3)'通过字符串及你的要求，可发现是以LV开头的。

v=split(s,",")'这个变量v即是你要的单独字符串。

'如用以下方式引用

for i=lbound(v) to ubound(v)

msgbox v(i)

next

---