CTE Recursive Hierarchy Order

Question

CTE Recursive Hierarchy Order

kiran 4243

Mr or Mrs. 500

Points: 553
More actions
April 28, 2018 at 1:59 am

#344533

Hi,
Please refer to the attached image for your kind perusal.
For example the product in level 1 contains multiple items as per the BOM list in further levels.
I tried to generate a hierarchy model as per the BOM list using recursive cte.
The hierarchy is displayed but the ordering is misplaced.
How to arrange the ordering?
Please advise.
Thank you.

Viewing 4 posts - 1 through 3 (of 3 total)

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

george_at_sql SSCommitted Points: 1967 More actions · Answer 1

kiran 4243 - Saturday, April 28, 2018 1:59 AM
Hi,
Please refer to the attached image for your kind perusal.
For example the product in level 1 contains multiple items as per the BOM list in further levels.
I tried to generate a hierarchy model as per the BOM list using recursive cte.
The hierarchy is displayed but the ordering is misplaced.
How to arrange the ordering?
Please advise.
Thank you.

george_at_sql SSCommitted Points: 1967 More actions · Answer 2

Do you happen to have an id field on the level2 column. such as level2_id that you wish to order by

If you do then you can order by the parentid, level1, followed by level2

If you don't then you would need to define the "ordering" of how you want to display the level2 columns. such as by level 2 name. or a substring of level 2 Name that contains just the numerical portion, for example.

with cte(col1, col2)
as (-- Query for CTE Hierarchy here..
)
select *
from cte
order by parentid,level
,substring(Name
,4
,patindex('%[^0-9]%',substring(Name,4,datalength(Name)))-1 /*First 4 characters are "GJ-", From the 4th Character i am looking for the first non numeric character to (using patindex) to extract the numerical portion of the data*/
)

Note:The records in a table are not ordered by anything, and therefore we cannot force that order in the desired query output.
Hope this helps

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

kiran 4243 - Saturday, April 28, 2018 1:59 AM
Hi,
Please refer to the attached image for your kind perusal.
For example the product in level 1 contains multiple items as per the BOM list in further levels.
I tried to generate a hierarchy model as per the BOM list using recursive cte.
The hierarchy is displayed but the ordering is misplaced.
How to arrange the ordering?
Please advise.
Thank you.

Please see the following article on how to control the order plus much more.
http://www.sqlservercentral.com/articles/Hierarchy/94040/

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)