Please help in writing a function

SQLServerCentral is supported by Red Gate Software Ltd.

Home Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Please help in writing a function

Please help in writing a function

Rate Topic

Display Mode

Topic Options

Author

Message

Prakash-485822

Posted Friday, July 03, 2009 6:10 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, July 08, 2009 2:23 AM
Points: 23, Visits: 103

Dear All

I have table with following data
Slno ParnetId Leg Orign Dest
1 1 1 V X
2 1 2 X Y
3 1 3 Y Z

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

Post #746952

Dave Ballantyne

Posted Friday, July 03, 2009 6:17 AM

SSChasing Mays

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 646, Visits: 1,872

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

Slow System ? Go Click First
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1
then
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2
to find the cause.

Slow Query ? Go Click
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
for details of how to post.

My Blog http://sqlblogcasts.com/blogs/sqlandthelike/

Post #746955

wim.buyens

Posted Friday, July 03, 2009 7:14 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 13, 2009 8:13 AM
Points: 193, Visits: 39

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)

Post #746981

Prakash-485822

Posted Sunday, July 05, 2009 12:40 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, July 08, 2009 2:23 AM
Points: 23, Visits: 103

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

Post #747308

Jeff Moden

Posted Sunday, July 19, 2009 11:25 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 10:12 PM
Points: 18,143, Visits: 12,162

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."

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #755503

« Prev Topic | Next Topic »

Permissions