Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hierarchical order by TSQL


Hierarchical order by TSQL

Author
Message
hifaizal90
hifaizal90
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 239
Dear Sir


create table Hierarchy
(
ParentID int,
ChildID int
)

go

insert into Hierarchy values (101,1),(1,6),(101,2),(2,4),(2,5),(5,6),(5,7),(101,3),(3,5)

Please refer the Hierarchical order Diagram in order to understand how this hierarchy order constructed.

we are focusing on filtering the parent ID.

when we pass the parameter 101 , output should display as follows from the Hierarchy table.

101/1/6
101/2/4
101/2/5/6
101/2/5/7
101/3/5/6
101/3/5/7

For 1 sql server output as follows

1/6

For 2 sql server output as follows

2/4
2/5/6
2/5/7

For 3 sql server output as follows

3/5/6
3/5/7

For 5 SQL Server output as follows

5/6
5/7
Attachments
hierarchy Order Picture.JPG (28 views, 21.00 KB)
Hierarchy table.JPG (23 views, 32.00 KB)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16540 Visits: 16993
Hi and welcome to the forums. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8487 Visits: 18089
Hi, Welcome to the forums.
I'll be glad to help you, but it would take me a while to prepare the data to be able to test any solution. Could you post your sample data as DDL and insert statements (in text not images)?

Meanwhile, I'll try to get a generic solution for you.


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
hifaizal90
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 239
I have updated my post . please refer the comments above.

Many thanks for your contribution and support towards to me .
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16540 Visits: 16993
hifaizal90 (2/5/2014)
I have updated my post . please refer the comments above.

Many thanks for your contribution and support towards to me .


Thanks for the updated info. The problem here is that you have what sort of appears to be an adjacency list but your data is not normalized.

Typically in these situations you have a row that has an ID. Then you have a ParentID column. You seem to have a parentid column and a childid column. You have nothing in your table that can function as a primary key.

Can you explain what ChildID is?

I think part of the issue here is that you have obfuscated the real information so much that it is nearly unusable.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8487 Visits: 18089
I might not be realizing the problem that Sean is talking about, but here's an example on how to do it. Be sure to understand what's going on before using it. And test it over a large amount of records as you might find that there can be performance problems.

DECLARE @Value int = 5;

WITH rCTE AS(
   SELECT CAST( CAST( ParentID AS varchar(10))+ '/' + CAST( ChildID AS varchar(10)) AS varchar(8000)) AS String,
      ParentID,
      ChildID
   FROM Hierarchy
   WHERE ParentID = @Value
   UNION ALL
   SELECT CAST( r.String + '/' + CAST( h.ChildID AS varchar(10)) AS varchar(8000)) As String,
      h.ParentID,
      h.ChildID
   FROM Hierarchy h
   JOIN rCTE r ON h.ParentID = r.ChildID
)
SELECT String
FROM rCTE r
WHERE NOT EXISTS( SELECT *
            FROM rCTE x
            WHERE x.String LIKE r.String + '%' --Contains the string
            AND x.String > r.String) --And is larger than the string
ORDER BY String




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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16540 Visits: 16993
Luis Cazares (2/5/2014)
I might not be realizing the problem that Sean is talking about...


Or maybe my brain is making it harder than it really is. :-)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
hifaizal90
hifaizal90
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 239
Dear Experts


@Luis Cazares\@Sean Lange: Thanks for your effort and indeed this is great piece of work ,Knowledge Sharing and outstanding support.

@Luis Cazares: This is an excellent script ..

As I can see first getting the relationship
SELECT CAST( CAST( ParentID AS varchar(10))+ '/' + CAST( ChildID AS varchar(10)) AS varchar(8000)) AS String,
      ParentID,
      ChildID
   FROM Hierarchy
   WHERE ParentID = @Value

Secondly It look for cross join with CTE child and main table sub class parent ...Based on this it keeps getting Main table child and the required output we are getting that is fantastic.

I have been told to extend this query based on the below information.

Could you please teach me Sir.

No parameters passing: when we select * from rcte

Please refer the attached file as if it's not clear.
The output should display as follow

P    Child Output
101    1    101/1/6
1    6    1/6
101    2    101/2/4
2    4    2/4
2    5    2/5/6
5    6    5/6
5    7    5/7
101    3    101/3/5
3    5    3/5
Attachments
HLRequirements.JPG (4 views, 35.00 KB)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16540 Visits: 16993
Just run the same query but comment out the where clause in the base part of the rCTE.


WITH rCTE AS(
   SELECT CAST( CAST( ParentID AS varchar(10))+ '/' + CAST( ChildID AS varchar(10)) AS varchar(8000)) AS String,
      ParentID,
      ChildID
   FROM Hierarchy
   --WHERE ParentID = @Value
   UNION ALL
   SELECT CAST( r.String + '/' + CAST( h.ChildID AS varchar(10)) AS varchar(8000)) As String,
      h.ParentID,
      h.ChildID
   FROM Hierarchy h
   JOIN rCTE r ON h.ParentID = r.ChildID
)
SELECT String
FROM rCTE r
WHERE NOT EXISTS( SELECT *
            FROM rCTE x
            WHERE x.String LIKE r.String + '%' --Contains the string
            AND x.String > r.String) --And is larger than the string
ORDER BY String



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
hifaizal90
hifaizal90
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 239
Dear Experts

I don't think we can use the same query.

In the first row we don't want output 101/1 for parentID=101,childID=1

Instead the output should be 101/1/6 in the first row since 1 has the branch 6.

Second row 1/6 remains the same since 6 doesn't have any branch

In the third row we want the output to be 101/2/4 not 101/2.

In the fourth row output 2/4 remains the same since 4 don't have any branch.

So it is filtering the Parent ID on row by row basis.

Please refer the output as follows and view the attached images for further information.

select * from CTE it should appear as follows includes output column for all rows.

P   C   
101   1   

output : 101/1/6

P   C
1   6   

output :1/6

P   C
101   2   

output :101/2/4

P   C
2   4

output :   2/4

P   C
2   5   

output :2/5/6

P   C
5   6   

output : 5/6

P   C
5   7   

output :5/7

P   C
101   3   

output :101/3/5

P   C
3   5   

output :3/5
Attachments
HLRequirements.JPG (111 views, 35.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search