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.