?
Solved

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

Posted on 2014-10-20
2
Medium Priority
?
111 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 35

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:BZ
ID: 40393027
Absolutely perfect. Thank you so much.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

718 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