Getting each parent in hierarchy in a row

If you want exactly three folders then you can left join data three times like here:

-- data
with 
  sheets(DocID, DocName, FolderId) as (
    select 12345, 'Abc.xlx',  98765 from dual union all
    select 12346, 'rst.xlx',  98764 from dual union all
    select 123457, 'jkl.xlx', 98763 from dual ),
  folders(FolderID, FolderName, ParentFolderId) as (
    select 98765, 'lmn', 98764 from dual union all
    select 98764, 'pqr', 98763 from dual union all
    select 98763, 'dcg', null  from dual)
-- end of data

select docname, f1.foldername child, f2.foldername parent, f3.foldername grand 
  from sheets s
  left join folders f1 on s.folderid = f1.folderid
  left join folders f2 on f2.folderid = f1.parentfolderid
  left join folders f3 on f3.folderid = f2.parentfolderid

Result:

DOCNAME CHILD PARENT GRAND
------- ----- ------ -----
Abc.xlx lmn   pqr    dcg
jkl.xlx dcg          
rst.xlx pqr   dcg 

You can also use recursive query, especially if there are more nested folders.

@PonderStibbons has shown how to do this with left outer joins; you could also use a hierarchical query and pivot the results:

with cte (FolderName, BaseFolderId, lvl) as (
  select FolderName, connect_by_root(FolderId), level
  from table2
  connect by FolderId = prior ParentFolderId
)
select *
from (
  select t1.DocName,
    c.FolderName,
    c.lvl
  from table1 t1
  join cte c on c.BaseFolderId = t1.FolderId
)
pivot (
  max(FolderName) for lvl in (1 as ChildFolder, 2 as ParentFolder, 3 as GrandparentFolder)
);

DOCNAME CHILDFOLDER PARENTFOLDER GRANDPARENTFOLDER
------- ----------- ------------ -----------------
jkl.xlx dcg                                       
Abc.xlx lmn         pqr          dcg              
rst.xlx pqr         dcg                           

or with recursive subquery factoring instead of a hierarchical query:

with rcte (FolderName, ParentFolderId, BaseFolderId, lvl) as (
  select FolderName, ParentFolderId, FolderId, 1 from table2
  union all
  select t2.FolderName, t2.ParentFolderId, r.BaseFolderId, r.lvl + 1
  from rcte r
  join table2 t2 on t2.FolderId = r.ParentFolderId
)
select *
from (
  select t1.DocName,
    r.FolderName,
    r.lvl
  from table1 t1
  join rcte r on r.BaseFolderId = t1.FolderId
)
pivot (
  max(FolderName) for lvl in (1 as ChildFolder, 2 as ParentFolder, 3 as GrandparentFolder)
);

DOCNAME CHILDFOLDER PARENTFOLDER GRANDPARENTFOLDER
------- ----------- ------------ -----------------
jkl.xlx dcg                                       
Abc.xlx lmn         pqr          dcg              
rst.xlx pqr         dcg                           

db<>fiddle

This sort of approach might be better as you add more levels of folder; but they all have to be defined in the pivot clause so it's still not entirely flexible.

If you want the folder IDs as well then include them in the hierarchy/CTE, and pivot both:

with rcte (FolderName, FolderId, ParentFolderId, BaseFolderId, lvl) as (
  select FolderName, FolderId, ParentFolderId, FolderId, 1 from table2
  union all
  select t2.FolderName, t2.FolderId, t2.ParentFolderId, r.BaseFolderId, r.lvl + 1
  from rcte r
  join table2 t2 on t2.FolderId = r.ParentFolderId
)
select *
from (
  select t1.DocName,
    r.FolderId,
    r.FolderName,
    r.lvl
  from table1 t1
  join rcte r on r.BaseFolderId = t1.FolderId
)
pivot (
  max(FolderName), max(FolderId) as id
  for lvl in (1 as ChildFolder, 2 as ParentFolder, 3 as GrandparentFolder)
);

DOCNAME CHILDFOLDER CHILDFOLDER_ID PARENTFOLDER PARENTFOLDER_ID GRANDPARENTFOLDER GRANDPARENTFOLDER_ID
------- ----------- -------------- ------------ --------------- ----------------- --------------------
jkl.xlx dcg                  98763                                                                    
Abc.xlx lmn                  98765 pqr                    98764 dcg                              98763
rst.xlx pqr                  98764 dcg                    98763                                       

Updated db<>fiddle