Tree structure sql example - 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 2012 Forums

Transact-SQL (2012)

Tree structure sql example

Author

Topic

madlo
Starting Member

41 Posts

Posted - 2014-12-09 : 13:17:09

I need output the tree flow of a table containing links between nodes

Input Table:
LocationType,Source, Location

quote:

'B','A1','B1'
'C','B1','C1'
'D','C1','D1'
'E','D1','E1'
'C','B2,'C2'
'D','B3','D3'
'E','D4','E4'
'B','A5','B5'
'B','A5,'B6'
'C','B6','C6'
'D','C6','D7'
'D','C7','D7'
'E','D7','E8'
'D',null,'D9'
'E','D9,'E9'

Assumptions that can be made:
'A' being the earliest root for a tree but ROOT can start at ANY node
Tree flows one direction ie. A < B < C < D < E
No backwards linking e.g. B can link to D but neverbackwards to A
Starting position is not always the same (not always A). Ending position is not always the same (can end sooner than E)
Multiple children can exist e.g. A5 to B5, A5 to B6
A node can have multiple sources e.g. C6 to D7, C7 to D7.
Source can be null. Location can never be null

Prefer best SQL performance as lots of data exist.

Desired Output: (order not important)
Columns: A,B,C,D,E
Rows:

quote:

'A1','B1','C1','D1',E1'
null,'B2','C2',null,null
null,'B3',null','D3',null
null,null,null','D4','E4'
'A5','B5',null',null,null
'A5','B6','C6','D7','E8'
null,null,'C7','D7','E8'
null,null,null,'D9','E9'

Test Data creation

CREATE TABLE Links
    (
      [LocationType] CHAR(1) ,
      [Source] VARCHAR(10) ,
      [Location] VARCHAR(10)
    )


CREATE TABLE Links
    (
      [LocationType] CHAR(1) ,
      [Source] VARCHAR(10) ,
      [Location] VARCHAR(10)
    )

INSERT INTO Links VALUES ('B','A1','B1')
INSERT INTO Links VALUES ('C','B1','C1')
INSERT INTO Links VALUES ('D','C1','D1')
INSERT INTO Links VALUES ('E','D1','E1')
INSERT INTO Links VALUES ('C','B2','C2')
INSERT INTO Links VALUES ('D','B3','D3')
INSERT INTO Links VALUES ('E','D4','E4')
INSERT INTO Links VALUES ('B','A5','B5')
INSERT INTO Links VALUES ('B','A5','B6')
INSERT INTO Links VALUES ('C','B6','C6')
INSERT INTO Links VALUES ('D','C6','D7')
INSERT INTO Links VALUES ('D','C7','D7')
INSERT INTO Links VALUES ('E','D7','E8')
INSERT INTO Links VALUES ('D',null,'D9')
INSERT INTO Links VALUES ('E','D9','E9')

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-09 : 13:25:57

What have you tried so far?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources