Hierarchical order by TSQL

Question

Hierarchical order by TSQL

Viewing 4 posts - 16 through 18 (of 18 total)

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

hifaizal90 SSC Veteran Points: 240 More actions · Answer 1

@v.collazos: I know it's quite frustrating since it doesn't have a relationship. You can introduce null in the another column define the relationship or some other ways if possible Please.

Luis Cazares SSC Guru Points: 183686 More actions · Answer 2

I should have asked this before. How many levels are you expecting?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

hifaizal90 SSC Veteran Points: 240 More actions · Answer 3

Dear Sir

In real data It goes up to 11 level . I would say not more than 12.

I have tried this but this is wrong anyway. It updates only for top parent not for the sub level.

drop table #table

go

create table #table (Value varchar(max), [ParentID] int)

go

create index ix_ParentID on #table (ParentID)

go

Insert into #table

select cast([ParentID] as varchar(5)) + '\' +

cast([childID] as varchar(5)) value, [ParentID]

from staging.Hierarchy

While (@@ROWCOUNT > 0)

Begin

update a

set a.value = cast(b.[ParentID] as varchar(5)) + '\' + a.value,

a.[ParentID] = b.[ParentID]

from #table a inner join staging.hierarchy b on a.[ParentID] = b.[childID]

End

insert into Hierarchy (Value , [ParentID] )

select * from #table

select * from staging.Hierarchy

hifaizal90 SSC Veteran Points: 240 More actions · Answer 4

Dear Sir

Is any possible solution for this sir..Please