All,

I'm currently suffering trying to "explode" and parent-child hierarchy for
optimisation purposes.  The structure is thus:

PK     Parent      VARCHAR(8)
PK     Child       VARCHAR(8)
PK     Adopt       DATETIME
       Abolish     DATETIME

What I want to get is an entity which looks similiar but is a full
extrapolation of the relationships (or rather where each child is mapped to
each of it's parents).

E.g. if my entity has the following tuples:

Parent     Child     Adopt          Abolish
A          A1        2005-12-01     NULL
A          A2        2005-12-01     NULL
A1         A11       2005-12-01     NULL
A1         A12       2005-12-01     NULL
A2         A21       2005-12-01     NULL
B          A         2005-12-01     NULL
B          C         2005-12-01     NULL
C          A         2005-12-01     NULL
C          D

I need to get to:

Parent     Child
A          A1
A          A11
A          A12
A          A2
A          A21
A1         A11
A1         A12
A2         A21
B          A
B          A1
B          A11
B          A12
B          A2
B          A21
B          C
B          D
C          A
C          A1
C          A11
C          A12
C          A2
C          A21
C          D

Can anyone please advise?  This has got me completely stumped.

Thanks in advance,


Adam M.

What, nobody put exploding hierachies on their wish list
for sql server 2005?

Show quoteHide quote

I think it's number 1 on all terrorist's Christmas lists.    :-)

In other words, less is more...and acts of omission are now features.
The more things change the more they stay the same :)

Happy Holidays,
www.rac4sql.net

My guess is that either you are sick of walking trees to do reporting
or you need to return results from a hierarchical search and you don't
want to walk each tree individually.  Anyway, I've had the same problem
in the past and have implemented your proposed solution with excellent
results so I wouldn't bother listening to the naysayers in the thread
(although I'd be interested to hear if anyone has a better idea for
handling reporting in large trees).  I have modified your proposed
result set to include a reference for each node in the tree to itself.
You'll have to trust me that this will make your life easier when you
go to join the resultset back to your tree.

Without further ado, here is the code for our straw man example:

-- ***********************************************
-- * Declare and populate local variables
-- ***********************************************
DECLARE @root int

DECLARE @iLoopBreak int, @oLoopBreak int


-- ***********************************************
-- * Declare intermediate data structures
-- ***********************************************
CREATE TABLE #tree ( par_id varchar(5)
                   , child_id varchar(5))

CREATE TABLE #flat_tree ( antecedent varchar(5)
                        , descendent varchar(5))


-- ***********************************************
-- * Seed tree
-- ***********************************************
INSERT INTO #tree (par_id, child_id) VALUES ('A', 'A1')
INSERT INTO #tree (par_id, child_id) VALUES ('A', 'A2')
INSERT INTO #tree (par_id, child_id) VALUES ('A1', 'A11')
INSERT INTO #tree (par_id, child_id) VALUES ('A1', 'A12')
INSERT INTO #tree (par_id, child_id) VALUES ('A2', 'A21')
INSERT INTO #tree (par_id, child_id) VALUES ('B', 'A')
INSERT INTO #tree (par_id, child_id) VALUES ('B', 'C')
INSERT INTO #tree (par_id, child_id) VALUES ('C', 'A')
INSERT INTO #tree (par_id, child_id) VALUES ('C', 'D')

-- ***********************************************
-- * Flatten tree
-- ***********************************************
INSERT INTO  #flat_tree VALUES ('B','B') --insert the root of all roots

SET @oLoopBreak = @@rowcount

WHILE @oLoopBreak <> 0 and @oLoopBreak < 100
BEGIN

INSERT INTO #flat_tree (antecedent, descendent)
SELECT ft.antecedent
      , t.child_id
   FROM #tree t
        JOIN #flat_tree ft
          ON ft.descendent = t.par_id
  WHERE NOT EXISTS (select '#'
                      from #flat_tree ft1
                     where ft1.antecedent = ft.antecedent
                       and ft1.descendent = t.child_id)

UNION

SELECT t.child_id
      , t.child_id
   FROM #tree t
        JOIN #flat_tree ft
          ON ft.descendent = t.par_id
  WHERE NOT EXISTS (select '#'
                      from #flat_tree ft1
                     where ft1.antecedent = t.child_id
                       and ft1.descendent = t.child_id)


SET @oLoopBreak = @oLoopBreak + 1

END


-- ***********************************************
-- * Return Results
-- ***********************************************
SELECT *
  FROM #flat_tree
  ORDER BY antecedent
         , descendent

-- ***********************************************
-- * Housekeeping
-- ***********************************************
DROP TABLE #tree
DROP TABLE #flat_tree

Sum Total Hours and Minutes in a report
Aggregate Function Error
unique variable per connection
RFC: EXISTS (SELECT ... FROM) optional?
use subquery result in Selet statement