Solved

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

Posted on 2014-10-20
2
107 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

756 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