Tip of the Day : How to Join with an Inline User-Defined Function
Get Tree Path Function

Get Tree Path Function

Let's say you have a table called [dbo].[Hierarchy] with the following data:

ID          ParentID    Name 
----------- ----------- --------------------------- 
1           0           Account Types
2           0           Card Types
3           1           Savings Account
4           1           Checking Account
5           1           Money Market Account
6           1           Certificate of Deposits
7           2           ATM Card
8           2           Charge Card
9           2           Credit Card
10          2           Debit Card

Given this table, you want to create the following output:

Path                                    Name
--------------------------------------- -----------------------
Account Types                           Account Types
Card Types                              Card Types
Account Types/Savings Account           Savings Account
Account Types/Checking Account          Checking Account
Account Types/Money Market Account      Money Market Account
Account Types/Certificate of Deposits   Certificate of Deposits
Card Types/ATM Card                     ATM Card
Card Types/Charge Card                  Charge Card
Card Types/Credit Card                  Credit Card
Card Types/Debit Card                   Debit Card

If the table only contains one level of hierarchy, this can be accomplished easily with the following statement:

SELECT ISNULL(B.[Name] + '/', '') + A.[Name] AS [Path], A.[Name]
FROM [dbo].[Hierarchy] A LEFT OUTER JOIN [dbo].[Hierarchy] B
                                      ON A.[ParentID] = B.[ID]

What if there's another level in the data, making it two levels as such:

ID          ParentID    Name                                               
----------- ----------- -------------------------------------------------- 
1           0           Account Types
2           0           Card Types
3           1           Savings Account
4           1           Checking Account
5           1           Money Market Account
6           1           Certificate of Deposits
7           2           ATM Card
8           2           Charge Card
9           2           Credit Card
10          2           Debit Card
11          9           Discover
12          9           Mastercard
13          9           Visa

Path                                    Name
--------------------------------------- -----------------------
Account Types                           Account Types
Card Types                              Card Types
Account Types/Savings Account           Savings Account
Account Types/Checking Account          Checking Account
Account Types/Money Market Account      Money Market Account
Account Types/Certificate of Deposits   Certificate of Deposits
Card Types/ATM Card                     ATM Card
Card Types/Charge Card                  Charge Card
Card Types/Credit Card                  Credit Card
Card Types/Debit Card                   Debit Card
Card Types/Credit Card/Discover         Discover
Card Types/Credit Card/Mastercard       Mastercard
Card Types/Credit Card/Visa             Visa

This can be accomplished by the following query:

SELECT ISNULL(C.[Name] + '/', '') + ISNULL(B.[Name] + '/', '') + A.[Name], A.[Name]
FROM [dbo].[Hierarchy] A LEFT OUTER JOIN [dbo].[Hierarchy] B
                                      ON A.[ParentID] = B.[ID]
                         LEFT OUTER JOIN [dbo].[Hierarchy] C
                                      ON B.[ParentID] = C.[ID]

These queries are useful if you know how many levels the table has.  What if you don't know the number of levels your table have?  What if your table goes as deep as 10 levels?  What can you do now?

Here's a useful user-defined function that can perform the task of generating the path of the current row.

CREATE FUNCTION [dbo].[ufn_GetParentPath] ( @pCurrentNodeID    INT )
RETURNS VARCHAR(1000)
AS
BEGIN

    DECLARE @vCurrentNodeName     VARCHAR(50)
    DECLARE @vParentID            INT

    IF @pCurrentNodeID IS NULL OR @pCurrentNodeID = 0
        RETURN NULL

    SELECT @vCurrentNodeName = [Name], @vParentID = [ParentID]
    FROM [dbo].[Hierarchy]
    WHERE [ID] = @pCurrentNodeID

    RETURN ISNULL([dbo].[ufn_GetParentPath] ( @vParentID ) + '/', '') + @vCurrentNodeName

END
GO

To use this user-defined function to return the desired output, simply issue the following SELECT statement:

SELECT [dbo].[ufn_GetParentPath] ( [ID] ) AS [Path], [Name]
FROM [dbo].[Hierarchy]

This function is straight-forward in the sense that it simply concatenates the name of the current node with the name of its parent.  The trick here is in the following code: 

RETURN ISNULL([dbo].[ufn_GetParentPath] ( @vParentID ) + '/', '') + @vCurrentNodeName

This step is returning the name of the parent concatenated with the name of the current node.  The function gets the name of the parent node by calling itself.  This kind of function is called a recursive function, which is a function that calls itself.  The function stops calling itself when the current node is already NULL or 0 and simply returns a NULL value.

This user-defined function can handle up to 31 levels of hierarchy.  This limitation is more of a limitation of SQL Server than the limitation of the function itself because SQL Server has a maximum of 32 nesting levels when calling functions or stored procedures.  Nesting occurs when one function calls another function, which is the case in this function.