basic sql query needed

[G Hopper]

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

[SKY2]

Re: basic sql query needed

is xlist always 7 char width? or will it have some thing like 123,1,333



rekcah@freeshel l.org (G Hopper) wrote in message news:<15b4c9ac. 0407271246.5cd9 1a6b@posting.go ogle.com>...[color=blue]
> 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[/color]

[Tony N.]

Re: basic sql query needed

rekcah@freeshel l.org (G Hopper) wrote in message news:<15b4c9ac. 0407271246.5cd9 1a6b@posting.go ogle.com>...[color=blue]
> 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[/color]

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(sub string(@str, @start,
len(@str) - @start + 1))))
break
end
else begin
insert into @vals values(@id, ltrim(rtrim(sub string(@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