Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Please help in writing a function Expand / Collapse
Author
Message
Posted Friday, July 3, 2009 6:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, June 8, 2014 6:58 AM
Points: 28, Visits: 124
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
Posted Friday, July 3, 2009 6:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 1,949, Visits: 8,311
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
Kent user group
Post #746955
Posted Friday, July 3, 2009 7:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 5:38 AM
Points: 237, Visits: 132
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
Posted Sunday, July 5, 2009 12:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, June 8, 2014 6:58 AM
Points: 28, Visits: 124
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
Posted Sunday, July 19, 2009 11:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 36,977, Visits: 31,494
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #755503
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse