--只有一个,分隔?select 字段,值=left(值,charindex(',',值+',')-1)
from 表
union all
select 字段,值=stuff(值,1,charindex(',',值),'')
from 表 where charindex(',',值)>0
order by 字段
from 表
union all
select 字段,值=stuff(值,1,charindex(',',值),'')
from 表 where charindex(',',值)>0
order by 字段
insert into test050202 select 'a','1,2'
union all select 'a','2,3'
union all select 'b','3'
union all select 'c','1'declare @s nvarchar(4000)set @s=''
select @s=@s+A+replace(B,',',','+A)+',' from test050202set @s=left(@s,len(@s)-1)
set @s='create table ##t (item nvarchar(10)) insert into ##t select '''
+replace(@s,',',''' union select ''')+''''
+' select left(item,1) as A,right(item,1) as B from ##t drop table ##t'
exec (@s)
drop table test050202
insert into test050202 select 'a','1,2'
union all select 'a','2,3'
union all select 'b','3'
union all select 'c','1'declare @s nvarchar(4000)
set @s=''
select @s=@s+' union select '''+A+''','
+replace(B,',',' union select '''+A+''',')
from test050202
set @s=stuff(@s,1,7,'')
exec (@s+' order by 1')drop table test050202/*--测试结果---- -----------
a 1
a 2
a 3
b 3
c 1
--*/
union all select 'a','c,2,3'
union all select 'b','3'
union all select 'c','1,d'
就会出错
union all select 'a','c,2,3'
union all select 'b','3766'
union all select 'c','1,ddd'
insert into test050202 select 'a','1,2'
union all select 'a','c,2,3'
union all select 'b','3'
union all select 'c','1,da'
declare @s nvarchar(4000)
set @s=''
select @s=@s+' union select '+quotename(A,'''')+','''
+replace(B,',',''' union select '+quotename(A,'''')+',''')+''''
from test050202
set @s=stuff(@s,1,7,'')
exec (@s+' order by 1')drop table test050202
insert into test050202 select 'a','1,2'
union all select 'a','2,3'
union all select 'bad','3'
union all select 'c','1rrrr'declare @s nvarchar(4000)
declare @len intset @s=''
select @len=len(A) from test050202
select @s=@s+A+replace('@'+B,',',','+A+'@')+',' from test050202set @s=left(@s,len(@s)-1)
print @s
set @s='create table ##t (item nvarchar(100)) insert into ##t select '''
+replace(@s,',',''' union select ''')+''''
+'select left(item,charindex(''@'',item)-1) as A,right(item,len(item)-charindex(''@'',item)) as B from ##t drop table ##t'
print @s
exec (@s)drop table test050202