Hierarchical (Recursive) Queries in SQL Server

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s