T-SQL Script - need Solution - SQL Server Forums

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)

T-SQL Script - need Solution

Author

Topic

ayfaizal
Starting Member

2 Posts

Posted - 2005-06-20 : 00:10:08

Could you pls. advice me on following dilemma

I am having table called ‘UNITCONVERTION’, its containing following records.


IDfrom 			FromValue		toValue 		IDto
-------------------------------------------------------------------------------------------------------------

2			1000			1		3
3			1000			1		4
4			1000			1		8
33			1000			1		38 	
103			2			1		205
56			1000			1		33
38			10			1		45
205			10			1		506
45			8			1		103

------------------------------------------------------------------------------------------------------------

IDfrom and IDto values are I am getting from another table called ‘UNITNAMES’

Example with first row

ID 2 is ‘Gram’
ID 3 is ‘KiloGram’ means I am using unitconvertion 1000 gram = 1 Kilogram

With above scenario, if i pass parameter within IDfrom or IDto, I need all the corresponding values and rows

For example (if parameter value is 38 I need rows like following order)



IDfrom 			FromValue		toValue 		IDto
-------------------------------------------------------------------------------------------------------------

56			1000			1		33
33			1000			1		38 	
38			10			1		45
45			8			1		103
103			2			1		205
205			10			1		506

------------------------------------------------------------------------------------------------------------

Anyone please help me with T-SQL scripting

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-06-20 : 05:45:12

Haven't done this kind of thing for a while, but look up 'Expanding Networks' in BOL.

Mark

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-20 : 10:01:21

I don't understand the question/problem.

>>parameter within IDfrom or IDto
if parameter is 38, why do you need the last 3 rows in your sample output?


IDfrom 			FromValue		toValue 		IDto
-------------------------------------------------------------------------------------------------------------

45			8			1		103
103			2			1		205
205			10			1		506

Be One with the Optimizer
TG

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-06-20 : 12:03:36

Hi TG
I think what he's after is any child / parent of the ID supplied at every level within the network. I.e. 45 is related to 38, 103 is related to 45, 205 is related to 103, etc.

Mark

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-20 : 13:49:17

ah, I see. How about this?


------------------------------------
--DDL, DML
set nocount on
declare @UNITCONVERsION table
	(IDfrom		int
	,FromValue	int
	,toValue	int
	,IDto		int)
insert @UNITCONVERsION
select 2,1000,1,3 union
select 3,1000,1,4 union
select 4,1000,1,8 union
select 33,1000,1,38 union
select 103,2,1,205 union
select 56,1000,1,33 union
select 38,10,1,45 union
select 205,10,1,506 union
select 45,8,1,103
------------------------------------
declare @parm int
set @parm = 38

declare @tb table 
	(Lev		int
	,IDfrom		int
	,FromValue	int
	,toValue	int
	,IDto		int)

declare @lev int
	,@IDto int

set @lev = 0

--insert Starting record
insert @tb
select	top 1 
	@lev
	,IDFrom
	,FromValue
	,toValue
	,IDto
from	@UNITCONVERsION
where	IDto = @parm
or	IDfrom = @parm


--work through backwards from starting pos
while @@rowcount > 0
begin
	select	@lev = @lev - 1

	insert @tb
	select	@lev
		,a.IDFrom
		,a.FromValue
		,a.toValue
		,a.IDto
	from	@UNITCONVERsION a
	join	@tb b	on a.IDto = b.IDfrom
			and b.lev = @lev + 1
end

--reset level to starting pos
select @lev = 0

--work through forwards from starting pos
while @@rowcount > 0
begin
	select	@lev = @lev + 1

	insert @tb
	select	@lev
		,a.IDFrom
		,a.FromValue
		,a.toValue
		,a.IDto
	from	@UNITCONVERsION a
	join	@tb b	on a.IDfrom = b.IDto
			and b.lev = @lev - 1
end

select * from @tb order by lev

Be One with the Optimizer
TG

ayfaizal
Starting Member

2 Posts

Posted - 2005-06-20 : 21:04:35

Thanks a lot TG, solutions working very fine…and i got more idea from that....once again thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources