Creating a tree - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Creating a tree

Author

Topic

dturner
Starting Member

24 Posts

Posted - 2011-09-02 : 17:06:56

Hi all,

I am stumped.
I have strings that can look like

1000091>1300435>1300437>1300462>
or
even
1000091>1300435>1300437>1300462>1200437>3300462>

What I would like to do is take that string and remove the last delimited part and enter the remainder into a table untill I get to the firts set of numbers.

So my table should look like:
1000091>1300435>1300437>1300462
1000091>1300435>1300437
1000091>1300435
1000091

Can some one help me out

The world has more information to offer than I can hold in my head

dturner
Starting Member

24 Posts

Posted - 2011-09-02 : 17:16:40

I Wanted to add some quick test data for anyone wanting to take a stab at it :)

CREATE TABLE MyTest (
ID varchar(100)
,PrimaryBranch varchar(100)
)
GO

Insert into MyTest Values
('86905','1000091>1300435>1300437>1300462>')

Insert into MyTest Values
('86906','1000091>1300435>1300437>1300462>1200437>3300462>')
---- THIS IS WHAT I WOULD LIKE IT TO LOOK LIKE
CREATE TABLE MyTest2 (
Level int
,ID varchar(100)
,PrimaryBranch varchar(100)
)
GO

Insert into MyTest2 Values
('4','86905','1000091>1300435>1300437>1300462')
Insert into MyTest2 Values
('3','86905','1000091>1300435>1300437')
Insert into MyTest2 Values
('2','86905','1000091>1300435')
Insert into MyTest2 Values
('1','86905','1000091')

SELECT * FROM MyTest2

The world has more information to offer than I can hold in my head

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-09-02 : 17:33:19

This is a way to do it using recursive common table expressions. If you will have rows with more than 100 tokens, add an OPTION (MAXRECURSION 500) or whatever the number is at the end of the query.

;WITH cte(Lvl,ID,PrimaryBranch) AS
(
    SELECT 1,Id, STUFF(PrimaryBranch, CHARINDEX('>', PrimaryBranch, 1), LEN(PrimaryBranch), '')
    FROM MyTest
    UNION ALL
    SELECT Lvl+1, t.Id, STUFF(t.PrimaryBranch, CHARINDEX('>', t.PrimaryBranch, LEN(c.PrimaryBranch) + 2), LEN(t.PrimaryBranch), '')
    FROM   cte c
    INNER JOIN MyTest t ON t.Id = c.Id
    WHERE  CHARINDEX('>', t.PrimaryBranch, LEN(c.PrimaryBranch) + 1) > 0
) 
SELECT *
FROM   cte 
WHERE PrimaryBranch IS NOT NULL
ORDER BY Id,Lvl Desc

dturner
Starting Member

24 Posts

Posted - 2011-09-02 : 17:43:15

Why was I making it so hard trying to use a cursor?
Thank you sunitabeck

The world has more information to offer than I can hold in my head

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources