|
|
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.
|