sql怎么将数据表中的一列按照值拆分为多列？

什么数据库？

createtable#(col1int,col2varchar(64))

go

insertinto#select1,'a`b`c`'

insertinto#select2,'d`e`f`'+char(13)+'g`h`l`'

insertinto#select3,'d`e`f`'+char(13)+'g`h`l`'+char(13)+'g`l`'

go

withcte1as(

selectcol1,tmpfrom

(

selectcol1,xmlns=cast(''asxml)

from#)p

crossapply

(

selecttmp=c.v.value('.','nvarchar(100)')

fromp.xmlns.nodes('/root/p')asc(v)

)s

)

selectcol1,

col2=left(tmp,1),

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

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

fromcte1

---------结果

col1col2col3col4

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

1abc

2def

2ghl

3def

3ghl

3gl

(6行受影响)