create table tb(id int,mytype varchar(400))
insert into tb
select 1,'a,b,c,d' union all
select 2, 'c,f,a' union all
select 3, 'g'
go
declare @id int,@mytype varchar(10)
declare @tb table (id int,mytype varchar(10))
declare cur cursor for
select id,mytype from tb
open cur
fetch next from cur into @id,@mytype
while @@fetch_status=0
begin
set @mytype=@mytype+','
while charindex(',',@mytype)>0
begin
insert into @tb values(@id,left(@mytype,charindex(',',@mytype)-1) )
set @mytype=right(@mytype,len(@mytype)-charindex(',',@mytype))
end
fetch next from cur into @id,@mytype
end
insert into tb
select 1,'a,b,c,d' union all
select 2, 'c,f,a' union all
select 3, 'g'
go
declare @id int,@mytype varchar(10)
declare @tb table (id int,mytype varchar(10))
declare cur cursor for
select id,mytype from tb
open cur
fetch next from cur into @id,@mytype
while @@fetch_status=0
begin
set @mytype=@mytype+','
while charindex(',',@mytype)>0
begin
insert into @tb values(@id,left(@mytype,charindex(',',@mytype)-1) )
set @mytype=right(@mytype,len(@mytype)-charindex(',',@mytype))
end
fetch next from cur into @id,@mytype
end
create table TT (
name varchar(10),
field1 varchar(50)
)insert TT select
-------------------
'a', '1'
union all select
'b', '2'
union all select
'c', '2,3'
union all select
'd', '1,2'go--用 动态语句
declare @sql varchar(8000)
set @sql='select '
select @sql=@sql+''''+name+''' as name,'''+replace(field1,',',''' as Field1 union all select '''+name+''' as name,''')+''' as Field1 union all select '
from TTset @sql=left(@sql,len(@sql)-17)exec( @sql)--结果
name Field1
---- ------
a 1
b 2
c 2
c 3
d 1
d 2--删除环境
drop table TT
create table fen(name nvarchar(10),field1 nvarchar(10))
------------------
insert into fen
select 'a', '1'
union select 'b', '2'
union select 'c', '2,3'
union select 'd', '1,2'declare @sql Nvarchar(4000)
set @sql=''
select @sql=@sql+' select '''+name+''' as name ,'''+replace(field1,',',''' as field1 union select '''+name+''' as name ,''') +''' as field1 union ' from fen
set @sql=left(@sql,len(@sql)-5)
print @sql
exec(@sql)a 1
b 2
c 2
c 3
d 1
d 2
from
(select name,course_id=convert(xml,'<root><v>'+replace(course_id,',','</v><v>')+'</v></root>') from yourTable)a
outer apply
(select field1=C.v.value('.','nvarchar(100)') from a.field1.nodes('/root/v')C(v))b
from
(select name,field1=convert(xml,'<root><v>'+replace(field1,',','</v><v>')+'</v></root>') from yourTable)a
outer apply
(select field1=C.v.value('.','nvarchar(100)') from a.field1.nodes('/root/v')C(v))b