是这样吗:
select --*,
distinct
case when vv like '%:%' then SUBSTRING(vv,charindex(':',vv)+1,len(vv))
else vv
end '编组'
from
(
select tname,
tstr,
v,
SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number) vv
from
(
select tname,
tstr,
SUBSTRING(t.tstr, number ,CHARINDEX(';',t.tstr+';',number)-number) v
from tbl t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(';'+t.tstr,s.number,1) = ';'
)t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.v,s.number,1) = ','
)t
/*
编组
0101
0102
0103
0104
*/
select --*,
distinct
case when vv like '%:%' then SUBSTRING(vv,charindex(':',vv)+1,len(vv))
else vv
end '编组'
from
(
select tname,
tstr,
v,
SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number) vv
from
(
select tname,
tstr,
SUBSTRING(t.tstr, number ,CHARINDEX(';',t.tstr+';',number)-number) v
from tbl t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(';'+t.tstr,s.number,1) = ';'
)t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.v,s.number,1) = ','
)t
/*
编组
0101
0102
0103
0104
*/
FROM
(
Select
a.tname,tstr=substring(substring(a.tstr,b.number,charindex(';',a.tstr+';',b.number)-b.number),4,LEN(substring(a.tstr,b.number,charindex(';',a.tstr+';',b.number)-b.number)))
from
Tb1 a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.tstr)
where
substring(';'+a.tstr,b.number,1)=';')t
SELECT * FROM
(
SELECT PARSENAME(REPLACE(tstr,',','.'),2) AS col FROM #tb
UNION
SELECT PARSENAME(REPLACE(tstr,',','.'),1) FROM #tb)t WHERE col IS NOT NULL
select 1 ,'01:0102;02:0102;03:0102;04:0102,0101;05:0102'insert into tbl
select 2,'01:0101,0102'insert into tbl
select 3,'01:0102;02:0102;03:0102;04:0102;05:0102'insert into tbl
select 4,'01:0102,0103'insert into tbl
select 5,'0104'insert into tbl
select 6,'01:0102;02:0102;03:0102;04:0102;05:0102'insert into tbl
select 7,'01:0102;02:0102;03:0102;04:0102,0101;05:0102'
go
存储过程:
create proc dbo.proc_group
asselect --*,
distinct
case when vv like '%:%' then SUBSTRING(vv,charindex(':',vv)+1,len(vv))
else vv
end '编组'
from
(
select tname,
tstr,
v,
SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number) vv
from
(
select tname,
tstr,
SUBSTRING(t.tstr, number ,CHARINDEX(';',t.tstr+';',number)-number) v
from tbl t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(';'+t.tstr,s.number,1) = ';'
)t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.v,s.number,1) = ','
)t
go
exec dbo.proc_group
/*
编组
0101
0102
0103
0104
*/
0101,0102,0103,0104得到一条记录(字符串),而不是一个数据集
这个在2000中,需要借助变量来实现:declare @str varchar(1000)set @str = ''select @str = @str + ','+ [编组]
from
(
select --*,
distinct
case when vv like '%:%' then SUBSTRING(vv,charindex(':',vv)+1,len(vv))
else vv
end '编组'
from
(
select tname,
tstr,
v,
SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number) vv
from
(
select tname,
tstr,
SUBSTRING(t.tstr, number ,CHARINDEX(';',t.tstr+';',number)-number) v
from tbl t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(';'+t.tstr,s.number,1) = ';'
)t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.v,s.number,1) = ','
)t
)tselect stuff(@str,1,1,'') as [编组]
/*
编组
0104,0101,0103,0102
*/
declare @str varchar(1000)set @str = ''select @str = @str + ','+ [编组]
from
(
select --*,
distinct
case when vv like '%:%' then SUBSTRING(vv,charindex(':',vv)+1,len(vv))
else vv
end '编组'
from
(
select tname,
tstr,
v,
SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number) vv
from
(
select tname,
tstr,
SUBSTRING(t.tstr, number ,CHARINDEX(';',t.tstr+';',number)-number) v
from tbl t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(';'+t.tstr,s.number,1) = ';'
)t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.v,s.number,1) = ','
)t
)t
order by [编组]select stuff(@str,1,1,'') as [编组]
/*
编组
0101,0102,0103,0104
*/