SQL 一行转多行（高精尖问题）
name   field1
-------------------
a   1
b   2
c   2,3
d   1,2
上表通过SQL语句转为
name   field1
-------------------
a   1
b   2
c   2
c   3
d   1
d   2

也就是说根据field1值以逗号隔开，形成多行。field1也可能是   1，2，3，4...

高手，研究帮我。谢谢   。。。


------解决方案--------------------
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
------解决方案--------------------
如果数据量不大，可以

--建立环境
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 TT

set @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

------解决方案--------------------
up
------解决方案--------------------

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