Prev: SELECT vs SET
Next: Using sp result of SELECT
From: ultradiv on 28 Apr 2010 12:57
Hi
There are two tables one with a varchar column with strings of integers that
relate to the other table's ID column I need to produce a join and result set

Here is some set up code describing the two tables with data and a third
table showing the needed results.

I also have a function that returns a table of the intgers in a comma
seperated string of integers - my question how can I use it in a join to
produce the result set?

Many thanks
Andy


declare @c int

CREATE TABLE #Table_S(
IDs int IDENTITY(1,1) NOT NULL,
strInts varchar(50) NULL)

Insert #Table_S(strInts)
values('1,3,6,11')

Insert #Table_S(strInts)
values('2,4,7,8,9,10')


CREATE TABLE #Table_P(
IDp int IDENTITY(1,1) NOT NULL,
ProName varchar(10) NULL)

set @c=1
while @c<21
begin
Insert #Table_P(ProName)
values('line'+cast(@c as varchar(10)))
set @c=@c+1
end


CREATE TABLE #Table_R(
IDp int,
ProName varchar(50)
)

Insert #Table_R(IDp,ProName)
select IDp, 'line'+cast(IDp as varchar(10)) from #Table_P where IDp in (1,3,6,
11)
Insert #Table_R(IDp,ProName)
select IDp, 'line'+cast(IDp as varchar(10)) from #Table_P where IDp in (2,4,7,
8,9,10)


select * from #Table_S

drop table #Table_S

select * from #Table_P

drop table #Table_P

select * from #Table_R

drop table #Table_R

the UDF:


CREATE FUNCTION [dbo].[iter_intlist_to_table] (@list varchar(4000))
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
number int NOT NULL) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@str nvarchar(4000),
@tmpstr nvarchar(4000),
@leftover nvarchar(4000)

SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen)
)
SET @textpos = @textpos + @chunklen

SET @pos = charindex(',', @tmpstr)
WHILE @pos > 0
BEGIN
SET @str = substring(@tmpstr, 1, @pos - 1)
INSERT @tbl (number) VALUES(convert(int, @str))
SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
SET @pos = charindex(',', @tmpstr)
END

SET @leftover = @tmpstr
END

IF ltrim(rtrim(@leftover)) <> ''
INSERT @tbl (number) VALUES(convert(int, @leftover))

RETURN
END

--use: iter_intlist_to_table(@ids) @ids as in the form of '1,2,3,4,5,6'

From: Erland Sommarskog on 28 Apr 2010 18:09
ultradiv (u59619(a)uwe) writes:
> There are two tables one with a varchar column with strings of integers
> that relate to the other table's ID column I need to produce a join and
> result set
>
> Here is some set up code describing the two tables with data and a third
> table showing the needed results.
>
> I also have a function that returns a table of the intgers in a comma
> seperated string of integers - my question how can I use it in a join to
> produce the result set?

If you are on SQL 2000, you can't, at least not in the general case,
but you need to run a cursor.

If you are on SQL 2000 or later, use the APPLY operator. See here for an
example: http://www.sommarskog.se/arrays-in-sql-2005.html#tablelists.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: SELECT vs SET
Next: Using sp result of SELECT