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 ««12

Hierarchical order by TSQL Expand / Collapse
Author
Message
Posted Friday, February 7, 2014 5:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 4:03 AM
Points: 8, Visits: 144
Dear Sir

The above query produces the required result even though 101 not appear in the parent Id since it is top level parent.

PID CID
1 6

output :1/6


1 6

output : 101/1/6

Many Thanks Experts.

Post #1539100
Posted Friday, February 7, 2014 8:18 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:43 PM
Points: 3,912, Visits: 8,858
hifaizal90 (2/7/2014)
Dear Sir

The above query produces the required result even though 101 not appear in the parent Id since it is top level parent.


Which one?



Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1539214
Posted Friday, February 7, 2014 8:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 23, 2014 9:45 AM
Points: 7, Visits: 76
I think your table definition and table data are wrong. Or may be I don't know in what case use your case.



That's because in a normal situation that includes parent/child you must to have a primary key, and a parent key. (ColumnID, ParentColumnID)

Your data has 2 parents for child 5 and 6. And I don't see a parent child relation, but a many to many relation. In your data you are limited to a max of 3 step cascade, in a relation (Id,parentId) you have unlimited steps.

In the third output row, your result must to be 101/2/4-5, so I think your original approach is wrong.

Anyway you can get your desired results making a cursor and building the output, but you gonna have problems like the third row


regards

Martin
Post #1539226
Posted Friday, February 7, 2014 9:18 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:43 PM
Points: 3,912, Visits: 8,858
I wonder why people consider the data structure is wrong.
If they are trying to store the structure of a process flow, where a task can have multiple successors and predecessors, would that be wrong?
We have no idea of the functionality of this data, so without further knowledge it's impossible to say if it's wrong or right.

I've been thinking on the possible solution, but I can't figure one out.

It seems that you edited the problem. Could you explain again what would be the input, the output and the rules to get it?



Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1539243
Posted Friday, February 7, 2014 9:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 23, 2014 9:45 AM
Points: 7, Visits: 76

It is not wrong to make a relationship of several predecessors and successors, but his logic contradicts the desired result, as with row 3 and 5 for example.

I'm just saying that if he wants to represent a parent child relationship is not the right way.
Post #1539256
Posted Friday, February 7, 2014 3:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 4:03 AM
Points: 8, Visits: 144
@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.



Post #1539414
Posted Friday, February 7, 2014 4:21 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:43 PM
Points: 3,912, Visits: 8,858
I should have asked this before. How many levels are you expecting?


Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1539432
Posted Saturday, February 8, 2014 3:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 4:03 AM
Points: 8, Visits: 144
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






  Post Attachments 
Hierarchy Picture.JPG (3 views, 35.59 KB)
Post #1539534
Posted Friday, February 14, 2014 10:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 4:03 AM
Points: 8, Visits: 144
Dear Sir

Is any possible solution for this sir..Please
Post #1541674
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse