build tree from child-to-parent table approach

Question

Post reply

build tree from child-to-parent table approach

mario17

SSCertifiable

Points: 6079
More actions
January 15, 2015 at 5:25 pm

#294384

Hi,
I'm playing with CTE and just want to expand my skills and ask how you would build this tree structure to fill that [Tree] column for table like in sample below:
/* CREATE TABLE #T1 (child_id INT, parent_id INT, tree VARCHAR(MAX))
INSERT INTO #T1 VALUES
( 200,3, '200-3-2-1' ),
( 100 , 14 , '100-14-1'),
( 3 , 2 , '3-2-1'),
( 2 , 1 , '2-1'),
( 14 , 1 , '14-1'),
( 1 , NULL , '1');
-- SELECT * FROM #t1 delete from #t1 */
WITH cte1 AS (
SELECT child_id, parent_id, CAST(child_id AS VARCHAR(10)) +'-'+ CAST(ISNULL(parent_id,'') AS VARCHAR(10)) [Tree2]
FROM #t1
WHERE parent_id IS NULL
UNION ALL
SELECT b.child_id, b.parent_id, CAST(b.child_id AS VARCHAR(10)) +'-'+ CAST(ISNULL(b.parent_id,'') AS VARCHAR(10)) [Tree2]
FROM #t1 b
INNER JOIN cte1 ON cte1.child_id = b.parent_ID
)
SELECT * FROM cte1
Tx Mario

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63509 More actions · Answer 1

I would look around for Jeff Moden's article(s) on hierarchies. Not sure if he uses a CTE or not.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

mario17 (1/15/2015)
Hi,
I'm playing with CTE and just want to expand my skills and ask how you would build this tree structure to fill that [Tree] column for table like in sample below:
/* CREATE TABLE #T1 (child_id INT, parent_id INT, tree VARCHAR(MAX))
INSERT INTO #T1 VALUES
( 200,3, '200-3-2-1' ),
( 100 , 14 , '100-14-1'),
( 3 , 2 , '3-2-1'),
( 2 , 1 , '2-1'),
( 14 , 1 , '14-1'),
( 1 , NULL , '1');
-- SELECT * FROM #t1 delete from #t1 */
WITH cte1 AS (
SELECT child_id, parent_id, CAST(child_id AS VARCHAR(10)) +'-'+ CAST(ISNULL(parent_id,'') AS VARCHAR(10)) [Tree2]
FROM #t1
WHERE parent_id IS NULL
UNION ALL
SELECT b.child_id, b.parent_id, CAST(b.child_id AS VARCHAR(10)) +'-'+ CAST(ISNULL(b.parent_id,'') AS VARCHAR(10)) [Tree2]
FROM #t1 b
INNER JOIN cte1 ON cte1.child_id = b.parent_ID
)
SELECT * FROM cte1
Tx Mario

Please see the following article.

http://www.sqlservercentral.com/articles/Hierarchy/94040/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)