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)
a1
b2
c2
c3
d1
d2
