如何根据一条数据生成多个记录，超恼头！

[wooden954]

在数据库中有一列数据，它是字符型，但实际存储的是个数未知的以逗号分隔的整数，如何根据这个整数生成多行记录？
Create Table Test
Data Varchar(30),
Name Varchar(20)
go

Insert into Test (Data,Name) Values('2,3,4,5','Test')
go

那么我希望在这种情况下无论是使用视图也好还是使用过程或函数也好，得到的结果是四行记录，即将Data的数据展开并变换为行记录
Data Name
－－－－－
2 Test
3 Test
4 Test
5 Test

[-晴天]

递归.

[fly_future]

create FUNCTION dbo.fn_split ( @inputstr varchar(8000), @seprator varchar(10) )
returns @temp table (a varchar(200))
as

begin
declare @i int

set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)

while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))

set @inputstr = substring(@inputstr, @i +1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end

if @inputstr <> ''
insert @temp values(@inputstr)

return
end

[fly_future]

其中 @inputstr 是例如‘1，2，3’的待分割的字符窜，@seprator 是分隔符如‘，’
将这个取出来了，其他的就好办了

[AcHerat]

Create Table Test(
  Data Varchar(30),
  Name Varchar(20)
)
go

Insert into Test (Data,Name) Values('2,3,4,5','Test')
go

select substring(a.data,b.number,charindex(',',a.data+',',b.number) - b.number) data,a.name
	into #t
from test a,master..spt_values b
where b.[type] = 'p' and b.number between 0 and len(a.data)
	and substring(','+a.data,b.number,1) = ','

truncate table test
insert into test(data,name)
select * from #t

select * from test

drop table test,#t


/*

Data                           Name
------------------------------ --------------------
2                              Test
3                              Test
4                              Test
5                              Test

(4 行受影响)

[AcHerat]

Create Table Test(
  Data Varchar(30),
  Name Varchar(20)
)
go

create trigger t_in on test
instead of insert
as
insert into test(data,name)
select substring(a.data,b.number,charindex(',',a.data+',',b.number) - b.number) data,a.name
from inserted a,master..spt_values b
where b.[type] = 'p' and b.number between 0 and len(a.data)
	and substring(','+a.data,b.number,1) = ','
go

Insert into Test (Data,Name) Values('2,3,4,5','Test')
go

select * from test


drop trigger t_in
drop table test,#t



/*

Data                           Name
------------------------------ --------------------
2                              Test
3                              Test
4                              Test
5                              Test

(4 行受影响)

[-晴天]

Create Table Test(
  Data Varchar(30),
  Name Varchar(20)
)
Insert into Test (Data,Name) Values('2,3,4,5','Test')
go
;with cte as(
select convert(int,left(data,charindex(',',data)-1))d,right(data,len(data)-charindex(',',data))+',' data,name from test
union all
select convert(int,left(data,charindex(',',data)-1)),right(data,len(data)-charindex(',',data)),name from cte where len(data)>1
)select d,name from cte
go
drop table test
/*
d           name
----------- --------------------
2           Test
3           Test
4           Test
5           Test

(4 行受影响)
*/

[Shawn]

Create Table Test
(
  Data Varchar(30),
  Name Varchar(20)
)
Insert into Test (Data,Name) Values('2,3,4,5','Test')
GO
--SQL:
SELECT B.data, A.[name] FROM 
(SELECT data = CAST('<R><v>' + REPLACE(Data, ',', '</v><v>') + '</v></R>' AS XML), [name] FROM TEST) A
CROSS APPLY
(SELECT data = R.v.value('.', 'nvarchar(100)') FROM A.data.nodes('/R/v') R(v)) B
/*
2	Test
3	Test
4	Test
5	Test
*/

[cd731107]

select Data=substring(Data,number,charindex(',',Data+',',number)-number), Test.name  from Test,master..spt_values 
WHERE  type='P' and substring(','+Data,number,1)=','

[wooden954]

非常感谢楼上各位，待俺测试一下！

[wooden954]

测试结果：
1楼：答案太简洁了，以至于俺看不懂
2楼：结果正确，但用到了临时表，算法逻辑上比较容易懂，
但考虑到我的实际用途，可能会有效率上的问题，同时
用到临时表的话我很难对临时表进行删除
3楼：同样是临时表的问题
4楼：呵呵，很浅显的道理哈
5楼：3楼的再次回复
6楼：可能是我的SQL版本太低了，我用的是2000，通不过语法检查
不过很想试试在高版本的情况下的效率问题，呵呵，有空的时候进行吧
7楼：有点儿可惜，仍然是我SQL版本低的原因。
8楼：不错，简洁，不依赖于临时表！喜欢！
再次感谢各位的解答，有些前提条件由于我没有写出来，所以让各位忙活半天而未被采用！
下次俺会注意的哈！