Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-10-20
2
Medium Priority
?
113 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:BAM
2 Comments
 
LVL 36

Accepted Solution

by:
ste5an earned 2000 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:BAM
ID: 40393027
Absolutely perfect. Thank you so much.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.
Suggested Courses

581 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