Solved

How do I build a hierarchical list using CTE (or other option)

Posted on 2014-10-20
2
109 Views
Last Modified: 2014-10-20
Before I begin, I have no control over the structure of this table. It belongs to a program my company uses. I cannot modify the structure of the existing tables. I have looked at many excellent solutions offered on EE, but none of them are quite getting me there. I have never used CTE before, but it seems the best option for this situation. However, I'm open to anything that works. Here is the CTE I put together based on solutions I found on EE for similar problems.

 ;WITH IH(ParentPart, Component)
      AS (SELECT
            ParentPart
                , Component
          FROM   BomStructure
          WHERE  ParentPart = '414E5000-911 REV 005'
          UNION ALL
          SELECT
            A.ParentPart
                , A.Component
          FROM   BomStructure A
                 INNER JOIN IH B
                         ON B.Component = A.ParentPart)
 SELECT
   ParentPart, Component
 FROM   IH

 I have a table with two fields, ParentPart and Component. When a Component part has children, then it is also listed as a ParentPart. If I query on a top level part, I get something like this (with some rows removed for brevity):

 Parent Part                            Component
 414E5000-911 REV 005                414E5001-6 REV A              
 414E5000-911 REV 005                414E5002-4 REV -              
414E5002-4 REV -                    414E5006-23 REV A            
 414E5002-4 REV -                    414E5008-1 REV -              
414E5002-4 REV -                    414E5008-5 REV -              
414E5002-4 REV -                    414E5010-1 REV -              
414E5002-4 REV -                    414E5010-2 REV -              
414E5002-4 REV -                    414E5012-1 REV A              
 414E5002-4 REV -                    414E5048-1 REV -              
414E5006-23 REV A                   414E5006-10 REV A            
 414E5011-6 REV -                    414E5011-8 REV -              

This is giving me the parent with its two children, and eventually it returns the children of the children, but this isn't what I need. I need parent - first child - all descendants of first child - second child - all descendants, etc.

 The actual hierarchy I need is:
414E5000-911 REV 005
414E5011-6 REV -
414E5005-1 REV -

414E5005-2 REV -
414ES102-1 REV B -

414E5002-4 REV -

414E5006-23 REV A
414E5006-6 REV A
414E5011-6 REV -
414E5011-8 REV -


 (This continues on through several more grandchildren and great grandchildren of the top level ParentPart).

 If CTE is the right way to go with this, would someone please help me get it right. If CTE isn't the best option, please make suggestions.

 Thanks!
0
Comment
Question by:BZ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 34

Accepted Solution

by:
ste5an earned 500 total points
ID: 40392714
You need an anchor value. E.g.

 
DECLARE @Parts TABLE 
( 
    ParentPart VARCHAR(255),
Component VARCHAR(255)
);

INSERT INTO @Parts
VALUES	( '414E5000-911 REV 005', '414E5001-6 REV A' ),              
	( '414E5000-911 REV 005', '414E5002-4 REV -' ),              
	( '414E5002-4 REV -', '414E5006-23 REV A' ),            
	( '414E5002-4 REV -', '414E5008-1 REV -' ),              
	( '414E5002-4 REV -', '414E5008-5 REV -' ),              
	( '414E5002-4 REV -', '414E5010-1 REV -' ),              
	( '414E5002-4 REV -', '414E5010-2 REV -' ),              
	( '414E5002-4 REV -', '414E5012-1 REV A' ),              
	( '414E5002-4 REV -', '414E5048-1 REV -' ),
	( '414E5006-23 REV A', '414E5006-10 REV A' ),          
	( '414E5011-6 REV -', '414E5011-8 REV -' ),
	( NULL, '414E5000-911 REV 005' );

WITH Hierarchy AS
	(
		SELECT	A.ParentPart,
			A.Component,	
			A.Component AS [Root],	
			0 AS [Level],
			'\\' + CAST(A.Component AS VARCHAR(MAX)) AS [Path]
		FROM	@Parts A
		WHERE	A.ParentPart IS NULL
		UNION ALL
		SELECT	C.Component AS [Root],	
			C.Component,
			P.[Root],
			P.[Level] + 1,
			P.[Path] + '\' + C.Component
		FROM	Hierarchy P
			INNER JOIN @Parts C ON P.Component = C.ParentPart
	)
	SELECT	H.[Root],			
		REPLICATE(' ', H.[Level] * 8) + H.Component
	FROM	Hierarchy H
	ORDER BY H.[Path];

Open in new window

0
 

Author Closing Comment

by:BZ
ID: 40393027
Absolutely perfect. Thank you so much.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question