Transform multiple rows into a single row in oracle SQL-CodePudding

Sample table

Expected results Trying to merge the related rows together into a single row.

Not sure what's wrong with my query. Trying to understand why it didn't work some scenarios (E.g. 01.11 but works for 19).

with                            
COLUMN 8 as (select COL F as COLUMN 8, COL B as COLUMN 7, COL C as COLUMN 22 from TABLE where COL F LIKE '%.__%'),                          
COLUMN 6 as (select COL E as COLUMN 6, COL B as COLUMN 5 from TABLE where COL E LIKE '%._%'),                           
COLUMN 4 as (select COL D as COLUMN 4, COL B as COLUMN 3, COL C as COLUMN 21 from TABLE),                           
COLUMN 2 as (select COL A as COLUMN 2, COL B as COLUMN 1 from TABLE)                            
select "COLUMN 8", "COLUMN 7", "COLUMN 6", "COLUMN 5", "COLUMN 4", "COLUMN 3", "COLUMN 2", "COLUMN 1"                           
from COLUMN 8 c                         
left join COLUMN 6 g on TO_CHAR(TRUNC(c.COLUMN 8,1)) LIKE TO_CHAR(g.COLUMN 6)                           
left join COLUMN 4 d on TRUNC(g.COLUMN 6,0) LIKE TO_CHAR(d.COLUMN 4)                            
left join COLUMN 2 s ON d.COLUMN 21 = s.COLUMN 2 OR s.COLUMN 2 = c.COLUMN 22;

CodePudding user response：

Try this (you may have to change the name of the columns in the PIVOT):

with data (code, name, root_code) as (
    select '01.11', 'sub sub crop', 'A' from dual union all
    select '01.1',  'sub crop', 'A' from dual union all
    select '01', 'crop', 'A' from dual union all
    select '19', 'Computer', 'C' from dual union all
    select '19.1', 'sub computer', 'C' from dual union all
    select '19.2', 'sub engine', 'C' from dual union all
    select '19.20', 'sub sub engine', 'C' from dual union all
    select '19.10', 'sub sub computer', 'C' from dual union all
    select 'A', 'Agriculture', null from dual union all
    select 'C', 'Technology', null from dual --union all
)
select class_code, class_name, sector_code, sector_name, division_code, division_name, grp_code, grp_name from (
    select l1, path, substr(path_item,1,instr(path_item,':')-1) as name, substr(path_item,instr(path_item,':') 1) as code-- , root  
    from (
        select lvl, level as l1, path, regexp_substr(path,'[^/] ',1,level) as path_item, root
        from (
            select level as lvl, d.code, d.name, sys_connect_by_path(name || ':' || code,'/') as path, connect_by_root(d.code) as root  
            from data d
            start with root_code is null
            connect by (
                (
                    (substr(code,1,length(code)-1) = prior code or substr(code,1,length(code)-1) = prior code || '.')
                    and prior root_code = root_code
                )
                or (
                    prior root_code is null and length(code) = 2
                    and root_code = prior code 
                )
            )
            and prior sys_guid() is not null
        ) d
        where lvl = 4
        connect by regexp_substr(path,'[^/] ',1,level) is not null and prior path = path and prior sys_guid() is not null
    )
)
pivot (
    max(name)as name, max(code) as code for l1 in (1 class,2 as sector,3 as division, 4 as grp)
);

A   Agriculture 01  crop    01.1    sub crop    01.11   sub sub crop
C   Technology  19  Computer    19.1    sub computer    19.10   sub sub computer
C   Technology  19  Computer    19.2    sub engine  19.20   sub sub engine