re****@freeshell.org (G Hopper) wrote in message news:<15**************************@posting.google. com>...
Ok, I'm a beginner so forgive me for my ignorance. Could someone help
me with this?
tbl_x has two fields xid and xlist
xlist being a list of numbers.....1, 5, 6, 8
i want to create a new table from tbl_x that converts each number in
that xlist into a row in tbl_new
so for example
tbl_x has 3 rows
xid xlist
1 1, 4, 5
2 2, 3, 7
3 2, 1, 7
i need a query or sql script that will convert that table with a list
into the following....
tbl_new
id xid xlid
1 1 1
2 1 4
3 1 5
4 2 2
5 2 3
6 2 7
7 3 2
8 3 1
9 3 7
It's not pretty but, this code works. I normally avoid cursors, but
couldn't get the function to work as a subquery in the from clause.
It should get you started.
create table t_ids
(xid int, xlist varchar(25))
go
create table t_ids2
(xid int, xl int)
go
insert into t_ids
select 1 as xid, '1, 4, 5'
union select 2, '2, 3, 7'
union select 3, '2, 1, 7'
GO
CREATE FUNCTION f_split(@id int, @str varchar(255), @sep char(1))
RETURNS @vals table (id int, val varchar(25))
as
begin
declare @start int,
@found int
set @start = 1
set @found = 0
while (@start > 0) begin
set @found = charindex(@sep, @str, @start)
if @found = 0 begin
if @start > 1
insert into @vals values(@id, ltrim(rtrim(substring(@str, @start,
len(@str) - @start + 1))))
break
end
else begin
insert into @vals values(@id, ltrim(rtrim(substring(@str, @start,
@found - @start))))
set @start = @found + 1
end
end
return
end
GO
DECLARE @id as int, @xl as varchar(25)
DECLARE c_xl CURSOR FAST_FORWARD
FOR SELECT xid, xlist FROM t_ids
open c_xl
FETCH c_xl into @id, @xl
WHILE @@Fetch_Status = 0 BEGIN
insert into t_ids2
select * from dbo.f_split(@id, @xl, ',')
FETCH c_xl into @id, @xl
END
CLOSE c_xl
select * from t_ids2
