Re: SQL: flattening a hierarchySKIP

In article <D95FsF.3ID_at_gremlin.nrtc.northrop.com>, maurit_at_world.nad.northrop.com (Mark W. Aurit) writes:
> While this is obviously play data, Im doing something like it for
> an application, and find it to be a real bitch.
> The input file lends itself to the Oracle connect by/start sql extension,
> which allows one to walk the file hierarchically. However,
> I need to rearraign the file to a seperate physical table to
> support listbox's and the like, and a flattened structure will be
> much more useful.
>
> The input is:
> Parent Child Level
> ------ ----- -----
> AllCars 1
> AllCars Ford 2
> Ford FullSize 3
> Ford Sports 3
> Sports Mustang 4
> Sports Probe 4
> Ford Utility 3
> FullSize Taurus 4
> Ford Vans 3
> Vans MiniVan 4
> AllCars GM 2
>
> I want to flatten it to something like:
> Level1 Level2 Level3
> ------ -------- -------
> Ford FullSize Taurus
> Ford Vans MiniVan
> Ford Utility Explorer
> Ford Sports Mustang
> Ford Sports Probe
> GM ................
>
> Thx, Mark
> --
> Mark Aurit
> Finance Client/Server Systems
> Northrop Grumman Data Systems (West)
> maurit_at_world.nad.northrop.com

If I've understood correctly, the following should help :

SELECT t1.child level1, t2.child level2, t3.child level3 WHERE t0.parent IS NULL
FROM table t0, table t1, table t2, table t3

WHERE  t0.child = t1.parent
AND    t1.child = t2.parent
AND    t2.child = t3.parent

If you don't know in advance how many levels you'll need, you'll need to use outer joins.

You can add extra levels by following the same logic, but each level requires an extra join. Provide for the maximum number of levels possible.

Incidentally, you could avoid two of these joins if you know your hierarchy has only 'ALLcars' as its root and the column 'level' you mention in your original table is available :

SELECT t1.parent level1, t1.child level2, t2.child level3 WHERE t1.level = 3
FROM table t1, table t2
WHERE t1.child = t2.parent Received on Fri May 26 1995 - 00:00:00 CEST