Matt Matt - 7 months ago 10
SQL Question

Oracle SQL 3 Level Structure from a single table

I have a table

STRUCTURE
which contains the following data when queried with:

SELECT *
FROM STRUCTURE
WHERE structure_id = 'LOCA'

structure_id name_value description level_no item_above structure_item_type_db Level_id level_description
LOCA 3000 North 4 300 1 4 Area
LOCA 827 North A 4 3000 2 4 Area
LOCA 828 North 1 4 3000 2 4 Area
LOCA 829 North 2 4 3000 2 4 Area
LOCA 830 North 3 4 3000 2 4 Area
LOCA 300 North & South 3 LOCA 1 3 Area Group
LOCA 3010 North Other 4 300 1 4 Area
LOCA 879 North Other 4 3010 2 4 Area


I have this code to get 2 Levels (Description and Area_Group) but I cannot get the 3rd level.

WITH CTE(NAME_VALUE, ITEM_ABOVE, DESCRIPTION, Area_Group) AS
(
SELECT NAME_VALUE, ITEM_ABOVE, DESCRIPTION, DESCRIPTION AS Area_Group
FROM STRUCTURE
WHERE ITEM_ABOVE = 'LOCA' AND Structure_id = 'LOCA'
UNION ALL
SELECT t1.NAME_VALUE, t1.ITEM_ABOVE, t1.DESCRIPTION, t2.Area_Group
FROM STRUCTURE t1
JOIN CTE t2 ON t1.ITEM_ABOVE = t2.NAME_VALUE
WHERE Structure_id = 'LOCA'
)
SELECT DESCRIPTION, Area_Group
FROM CTE


Gives me:

description Area_Group
North North & South
North A North & South
North 1 North & South
North 2 North & South
North 3 North & South
North & South North & South
North Other North & South
North Other North & South


I want to output the data (
WHERE structure_id = 'LOCA'
is needed as there are other structures that aren't used in this scenario) to look like this:

description Area_Group Area
North North & South North
North A North & South North
North 1 North & South North
North 2 North & South North
North 3 North & South North
North & South North & South North
North Other North & South North Other
North Other North & South North Other


So if we imagine the structure as a tree structure...

Area_Group - North & South
└ Area - North
└ description - North A
description - North 1
description - North 2
description - North 3
└ Area - North Other
└ description - North Other

Answer

This should help, two self-joins and CASE WHEN statements:

WITH
  sa AS (SELECT * 
         FROM structure 
         WHERE structure_id = 'LOCA'),
  sb AS (SELECT s1.description, s1.name_value, s1.level_id,
         CASE WHEN s1.structure_item_type_db = 1
              THEN s1.description 
              ELSE s2.description
         END area,
         CASE WHEN s1.structure_item_type_db = 1
              THEN s1.item_above 
              ELSE s2.item_above
         END item_above
         FROM sa s1 
         LEFT JOIN sa s2 ON s2.name_value = s1.item_above)
         SELECT s1.description, 
         CASE WHEN s1.level_id = 3 
              THEN s1.description 
              ELSE s2.description 
         END area_group, s1.area
         FROM sb s1 
         LEFT JOIN sb s2 ON s2.name_value = s1.item_above

SQL Fiddle demo

In your data, in row 6 item_above has value 3500, and there is no row with such name_value, so left join attaches nothing here. Column ID added only for presentation purposes.

Output:

   DESCRIPTION     AREA_GROUP      AREA
 --------------- --------------- ---------------
   North           North & South   North
   North A         North & South   North
   North 1         North & South   North
   North 2         North & South   North
   North 3         North & South   North         
   North & South   North & South   North & South
   North Other     North & South   North Other
   North Other     North & South   North Other