This code sample demonstrates recursive queries in SQL Server.

The query returns paths of Kettle transformations.

with Parent as
(
   select
      ID_DIRECTORY
   ,  ID_DIRECTORY_PARENT
   ,  cast(DIRECTORY_NAME
         as varchar(255)) as DIRECTORY_PATH
   from
      R_DIRECTORY
   where
      ID_DIRECTORY_PARENT = 0
   union all
   select
      TH.ID_DIRECTORY
   ,  TH.ID_DIRECTORY_PARENT
   ,  cast(Parent.DIRECTORY_PATH
         + '/' + DIRECTORY_NAME
         as varchar(255)) as DIRECTORY_PATH
   from
      R_DIRECTORY TH
   inner join
      Parent
   on
      Parent.ID_DIRECTORY = TH.ID_DIRECTORY_PARENT
)
select
   coalesce(dir.DIRECTORY_PATH, '/') DIRECTORY_PATH
,  trans.NAME as TRANSFORMATION_NAME
from
   Parent dir
right join
   R_TRANSFORMATION trans
on
   dir.ID_DIRECTORY = trans.ID_DIRECTORY