Solved

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

Posted on 2014-10-20
2
106 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
2 Comments
 
LVL 33

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

856 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