CODEWITHSUNDEEP
sqloracle-databasehierarchyhierarchical-data
AlexOk
AlexOk

Reputation: 35

How to extract a hierarchical structure from a flat table?

I want to extract a hierarchical structure from a table in the oracle database. Table look like this:

+----+--------+----------------+---------------------+
| id | lvl1   | lvl2           | lvl3                | 
+----+--------+----------------+---------------------+
| 1  | Oracle | Marketing unit | Internet            | 
+----+--------+----------------+---------------------+
| 2  | Oracle | Lawyers unit   | Intellectual        | 
+----+--------+----------------+---------------------+
| 3  | Oracle | Finance unit   | null                |
+----+--------+----------------+---------------------+
| 4  | Oracle | Lawyers unit   | Judicial department | 
+----+--------+----------------+---------------------+
| 5  | Oracle | IT unit        | Database            |
+----+--------+----------------+---------------------+
| 6  | Oracle | Marketing unit | Television          | 
+----+--------+----------------+---------------------+
| 7  | Oracle | IT unit        | ERP                 | 
+----+--------+----------------+---------------------+

That's what I want to get:

- Oracle
. - Marketing unit
.   - Internet
.   - Television 
. - Lawyers unit
.   - Intellectual
.   - Judicial department
. - Finance unit
. - IT unit
.   - Database
.   - ERP

I read about Oracle Hierarchical Queries, but i have no idea how to made it with my table structure... Finally, I have to get JSON to display on the web page. I prepared a table on sqlfiddle for convenience

I would be grateful for the help, any ideas?

Upvotes: 2

Views: 370

Answers (1)

Radagast81
Radagast81

Reputation: 3006

One way is the following (using UNPIVOT):

WITH unpiv AS (SELECT lvl, par, cur, MIN(id) id FROM HIERARCHY_SAMPLE
               unpivot ((par, cur) for lvl in ((lvl1 /*Something here - it doesn't really matter */,lvl1) as 1
                                              ,(lvl1,lvl2) as 2
                                              ,(lvl2,lvl3) as 3))
                WHERE cur IS NOT NULL
               GROUP BY lvl,par,cur)
SELECT LPAD('- ', LEVEL*2)||cur 
  FROM unpiv
START WITH lvl = 1
CONNECT BY lvl = PRIOR lvl + 1
       AND par = PRIOR cur
ORDER siblings BY id

Upvotes: 3

Related Questions