Please help in writing a function

Question

Post reply

Please help in writing a function

Prakash-485822

SSC Eights!

Points: 982
More actions
July 3, 2009 at 6:10 am

#71733

Dear All
I have table with following data
SlnoParnetIdLegOrignDest
11 1 VX
21 2 XY
31 3 YZ
I have to write a fucntion which will take PraentId as parameter and return the following output
select fn_GetTripDetails(1)
output:V-X-Y-Z

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 1

Hi ,

start with this article http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/.

I would advise against using a scalar function and create a inline table function for perfomance reasons

Clear Sky SQL
My Blog[/url]

wim.buyens Ten Centuries Points: 1159 More actions · Answer 2

if object_id('dbo.tmpTable') is not null

drop table dbo.tmpTable

go

create table dbo.tmpTable(Slno int, ParentId int, Leg int, Orign char(1), Dest char(1))

insert into dbo.tmpTable

select 1, 1, 1, 'V', 'X'

union all select 2, 1, 2, 'X','Y'

union all select 3, 1, 3, 'Y','Z'

go

--when you can use Leg as the order

declare @concat nvarchar(max)

select @concat=coalesce(@concat,Orign)+'-'+Dest

from dbo.tmpTable where Parentid=1 order by Leg

select @concat

--or order is not known:

if object_id('dbo.fn_GetTripDetails') is not null

drop function dbo.fn_GetTripDetails

go

create function dbo.fn_GetTripDetails(@ParentID int)

returns nvarchar(max)

as

begin

declare @concat nvarchar(max)

;with Tab ([Concat],[Last],lvl)

as

(select convert(nvarchar(max),Orign+'-'+Dest),Dest,0 as lvl

from dbo.tmpTable t1 where ParentId=@ParentID and not exists

(select * from dbo.tmpTable t2 where t1.Orign=t2.Dest and t1.ParentiD=t2.ParentID)

union all

select [Concat]+'-'+t2.Dest,t2.dest,t.lvl+1

from Tab t

inner join dbo.TmpTable t2

on t.Last=t2.Orign

and t2.ParentiD=@ParentID

)

select top 1 @concat=[Concat] from Tab order by lvl desc;

RETURN @concat

end

go

select dbo.fn_GetTripDetails(1)

Prakash-485822 SSC Eights! Points: 982 More actions · Answer 3

Thanks for your solution.

It is working fine in Sql server 2005.

But problem is we are using Sql server 2000

Can provide me the soultion in Sql server 2000

regards

Prakash

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 4

Lookup "Expanding Hierarchies" in SQL Server 2000 Books Online.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)