关于字符串拆分，合并问题的整理(二)
t
COl2=(select N','+Col2 from Tab where Col1=a.COl1
For XML PATH(''), ROOT('R'), TYPE))b
--方法2:
select
a.Col1,
COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44))
from
(select distinct COl1 from Tab) a
cross apply
(
select
Col2=(select COl2 from Tab where COl1=a.COl1 FOR XML AUTO, TYPE)
.query('
{for $i in /Tab[position()
{concat("",string(/Tab[last()]/@COl2))}
')
)b
--SQL05用CTE:
www.2cto.com
;with roy as(
select
Col1,
Col2,
row=row_number()over(partition by COl1 order by COl1)
from
Tab
)
,Roy2 as
(
select
COl1,
cast(COl2 as nvarchar(100))COl2,row
from
Roy
where
row=1
union all
select
a.Col1,
cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row
from
Roy a
join
Roy2 b
on
a.COl1=b.COl1 and a.row=b.row+1
)
select
Col1,
Col2
from
Roy2 a
where
row=(
select
max(row)
from
roy
where Col1=a.COl1
)
order by
Col1
option (MAXRECURSION 0)
生成结果:
/*
Col1 COl2
----------- ------------
1 a,b,c
2 d,e
3 f
(3 行受影响)
*/
www.2cto.com
--> --> (Roy)生成
if not object_id('Tab') is null
drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go
--SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
drop table #Num
go www.2cto.com
select
top 100 ID=Identity(int,1,1) into #Num
from
syscolumns a,
syscolumns b
Select
a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID)
from
Tab a,
#Num b
where
charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
--2000不使用辅助表
Select
a.Col1,
COl2=substring(a.Col2,b.number,
charindex(',',a.Col2+',',b.number)-b.number)
from
Tab a
join
master..spt_values b
ON
B.type='p'
AND B.number BETWEEN 1 AND LEN(A.col2)
where
substring(','+a.COl2,b.number,1)=','
www.2cto.com
--Xml方法
select
a.COl1,b.Col2
from
(
select
Col1,
COl2=convert(xml,''
+replace(COl2,',','')+' ')
from Tab
)a
outer apply
(
select
Col2=C.v.value('.','nvarchar(100)')
from
a.COl2.nodes('/root/v')C(v)
)b
www.2cto.com
/*
Col1 COl2
----------- -----
1 a
1 b
1 c
2 d
2 e
3 f
*/
作者 TravyLee