How to make col of 2nd Query the col of 1st

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to make col of 2nd Query the col of 1st

Author

Topic

real_pearl
Posting Yak Master

106 Posts

Posted - 2005-01-14 : 03:46:49

CREATE FUNCTION UDF_SPLIT(@gec varchar(255))
RETURNS @BINS TABLE (bin int)
AS
BEGIN
DECLARE @separator char(1),
@separator_position int,
@array_value varchar(1000)

SELECT @separator = ','

SET @gec = @gec + @separator

WHILE PATINDEX('%' + @separator + '%' , @gec) <> 0
BEGIN
SELECT @separator_position = PATINDEX('%' + @separator + '%' , @gec)
SELECT @array_value = LEFT(@gec, @separator_position - 1)

INSERT INTO @BINS SELECT Array_Value = @array_value

SELECT @gec = STUFF(@gec, 1, @separator_position, '')
END

RETURN
END

We have a function that splits the string into a single value

So we have 2 string one for x-position and second for y-position

select * from udf_split('3,4,5') --values for x-position
select * from udf_split ('3,3,6') --values for y-position

now I want to have the result as

X Y
3 3
4 3
5 6

please tell me how to achieve this

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-14 : 05:58:05

Change the return to
RETURNS @BINS TABLE (bin int, id int identity)

then you can join the two returned tables on the id.

Otherwise you can pas both strings to the function, duplicate all the code inside the while loop for the other string and isert the two values into the table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

real_pearl
Posting Yak Master

106 Posts

Posted - 2005-01-14 : 06:20:56

I had this idea but for this I'll have to change the function, which I am calling at some other places too. I wanted to not change the function and get the desired results.

With this solution I'll make another same function but with different columns.

Any how thanks a lot :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-14 : 10:16:11

you could leave the function as is, and do this:

select *, IDENTITY(1,1) as Position
into #Temp1
from udf_split ('3,3,6')

select *, IDENTITY(1,1) as Position
into #Temp2
from udf_split ('1,2,3')

and then join #TEmp1 to #Temp2 on the Position column. Same as what Nigel says, but this way you don't have to change your UDF.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources