How to make recursive cte SQL with 3 hierarchy levels ?

Question

0.00/5 (No votes)

See more:

i have a table inside my sql server Express 2014 called ChartOfAccountsTree. what i want to call my accounts inside that table with hierarchy view . first of all i have MainTree ... then inside it there is assets . then inside assets there is lands.
what i want to do is to call thes accounts like this (Lands/Assets/MainTree).
this is the code of creating the table

SQL

/****** Object:  Table [dbo].[Tbl_ChartOfAccountsTree]    Script Date: 27/12/2019 10:18:01 م ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tbl_ChartOfAccountsTree](
	[Chart_ID] [int] NOT NULL,
	[Chart_Name] [nvarchar](500) NULL,
	[Account_Level] [int] NULL,
	[ParentAccount] [int] NULL,
	[Accoutn_Type] [nvarchar](150) NULL,
	[Direction] [nvarchar](150) NULL,
	[OB] [decimal](18, 2) NULL,
	[Date] [date] NULL,
 CONSTRAINT [PK_Tbl_ChartOfAccountsTree] PRIMARY KEY CLUSTERED 
(
	[Chart_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT [dbo].[Tbl_ChartOfAccountsTree] ([Chart_ID], [Chart_Name], [Account_Level], [ParentAccount], [Accoutn_Type], [Direction], [OB], [Date]) VALUES (0, N'MainTree', 0, NULL, N'Parent', NULL, CAST(0.00 AS Decimal(18, 2)), CAST(N'2019-07-01' AS Date))
GO
INSERT [dbo].[Tbl_ChartOfAccountsTree] ([Chart_ID], [Chart_Name], [Account_Level], [ParentAccount], [Accoutn_Type], [Direction], [OB], [Date]) VALUES (1000, N'Assets', 1, 0, N'Parent', N'Balance', CAST(0.00 AS Decimal(18, 2)), CAST(N'2019-07-01' AS Date))
GO
INSERT [dbo].[Tbl_ChartOfAccountsTree] ([Chart_ID], [Chart_Name], [Account_Level], [ParentAccount], [Accoutn_Type], [Direction], [OB], [Date]) VALUES (1100, N'FixedAssets', 2, 1000, N'Parent', N'Balance', CAST(0.00 AS Decimal(18, 2)), CAST(N'2019-07-01' AS Date))
GO
INSERT [dbo].[Tbl_ChartOfAccountsTree] ([Chart_ID], [Chart_Name], [Account_Level], [ParentAccount], [Accoutn_Type], [Direction], [OB], [Date]) VALUES (1101, N'Buildings', 3, 1100, N'Child', N'Balance', CAST(0.00 AS Decimal(18, 2)), CAST(N'2019-11-01' AS Date))
GO
INSERT [dbo].[Tbl_ChartOfAccountsTree] ([Chart_ID], [Chart_Name], [Account_Level], [ParentAccount], [Accoutn_Type], [Direction], [OB], [Date]) VALUES (1102, N'Lands', 3, 1100, N'Child', N'Balance', CAST(0.00 AS Decimal(18, 2)), CAST(N'2019-11-29' AS Date))
GO
ALTER TABLE [dbo].[Tbl_ChartOfAccountsTree]  WITH CHECK ADD  CONSTRAINT [FK_Tbl_ChartOfAccountsTree_Tbl_ChartOfAccountsTree] FOREIGN KEY([ParentAccount])
REFERENCES [dbo].[Tbl_ChartOfAccountsTree] ([Chart_ID])
GO
ALTER TABLE [dbo].[Tbl_ChartOfAccountsTree] CHECK CONSTRAINT [FK_Tbl_ChartOfAccountsTree_Tbl_ChartOfAccountsTree]
GO

the problem is when i call the column Chart_Name it gives me
(Lands/Assets)

What I have tried:

SQL

;
WITH HierarchyList_CTE
AS
(
SELECT    Chart_ID, Chart_Name, ParentAccount, 1 AS StaffLevel
FROM      Tbl_ChartOfAccounts
WHERE     ParentAccount IS NULL
UNION ALL
SELECT         ST.Chart_ID, ST.Chart_Name
              , ST.ParentAccount, StaffLevel + 1
FROM          Tbl_ChartOfAccounts ST
INNER JOIN    HierarchyList_CTE CTE
              ON ST.ParentAccount = CTE.Chart_ID
)
SELECT         stf.Chart_ID,
              CTE.Chart_Name + ' / '+ STF.Chart_Name
               AS FullName
              ,CTE.StaffLevel ,CTE.ParentAccount
FROM          HierarchyList_CTE CTE
INNER JOIN    Tbl_ChartOfAccounts STF
              ON STF.ParentAccount = CTE.Chart_ID

can any one help me Plz

Posted 27-Dec-19 9:27am

Abuamer

Updated 28-Dec-19 23:36pm

Patrice T

v3

Add a Solution

2 solutions

Solution 1

If I understand the question correctly, you could build a 'path' column in your CTE

Consider the following example

Create a test table and fill it with some data

SQL

create table locations (
   id int,
   parentid int,
   place varchar(100)
)

insert into locations (id, parentid, place) values 
(1, null, 'Europe'),
(2, 1, 'France'),
(3, 2, 'Paris'),
(4, 2, 'Marseille'),
(5, 2, 'Lyon'),
(6, 1, 'Italy'),
(7, 6, 'Rome'),
(8, 6, 'Milan'),
(9, 6, 'Venice'),
(10, 1, 'United Kingdom'),
(11, 10, 'London'),
(12, 10, 'Cambridge'),
(13, 10, 'Bath')

Query the data

SQL

with Places (id, parentid, place, level, path) AS (
   select l.id, 
          l.parentid, 
	      l.place, 
		  1 as level,
	      cast('Continent' as varchar(1000))
   from locations l
   where l.parentid is null
   union all
   select l.id, 
          l.parentid, 
	      l.place, 
		  p.level + 1,
		  cast(case p.level 
		       when 1 then p.path + '/Country'		     
			   when 2 then p.path + '/City'		     
		    end as varchar(1000))
   from locations l
        inner join places p on p.id = l.parentid
)
select * 
from places
order by id

the result would be

id   parentid   place            level   path
--   --------   -----            -----   --------
1    NULL       Europe           1       Continent
2    1          France           2       Continent/Country
3    2          Paris            3       Continent/Country/City
4    2          Marseille        3       Continent/Country/City
5    2          Lyon             3       Continent/Country/City
6    1          Italy            2       Continent/Country
7    6          Rome             3       Continent/Country/City
8    6          Milan            3       Continent/Country/City
9    6          Venice           3       Continent/Country/City
10   1          United Kingdom   2       Continent/Country
11   10         London           3       Continent/Country/City
12   10         Cambridge        3       Continent/Country/City
13   10         Bath             3       Continent/Country/City

[ADDED AN ALTERNATE EXAMPLE]

Regarding the football player example. The idea is the same as in the previous query. During each recursive loop, concatenate the values as a path to the result set.

Consider the following

The data

SQL

create table person (
   id int,
   fatherid int,
   name varchar(100)
)


insert into person (id, fatherid, name) values 
(1, null, 'Aveiro'),
(2, 1, 'Dos Santos'),
(3, 2, 'Ronaldo'),
(4, 3, 'Cristiano')

The query

SQL

with PersonHier (id, fatherid, name, level, fullname) AS (
   select p.id, 
          p.fatherid, 
	      p.name, 
		  1 as level,
	      cast(p.name as varchar(max))
   from person p
   where p.fatherid is null
   union all
   select p.id, 
          p.fatherid, 
	      p.name, 
		  h.level + 1,
		  concat(p.name, ' / ', h.FullName)
   from person p
        inner join PersonHier h on h.id = p.fatherid
)
select * 
from PersonHier
order by id

the result

id   fatherid   name       level   fullname
--   --------   -----      -----   ------------
1    NULL       Aveiro     1       Aveiro
2    1          Dos Santos 2       Dos Santos / Aveiro
3    2          Ronaldo    3       Ronaldo / Dos Santos / Aveiro
4    3          Cristiano  4       Cristiano / Ronaldo / Dos Santos / Aveiro

Posted 27-Dec-19 10:10am

Wendelius

Updated 28-Dec-19 21:49pm

v2

Comments

Abuamer 28-Dec-19 3:29am

thank you my friend . but i am sorry it did not solve the problem. so please help me .

Wendelius 28-Dec-19 4:00am

Could you explain the problem in more detail, what is the expected output?

Abuamer 28-Dec-19 4:51am

OK I will Give You An Example For What I want In More Details. Cristiano Ronaldo Is Agreat Player And His Full Name Is (Cristaino Ronaldo Dos Santos Aveiro).
Aveiro Is the grand Grand Father with level 0.
Dos Santos is the son Of Aveiro with level 1.
Ronaldo Is the son of Dos Santos with level 2.
And Finally Cristiano Is The Son of Ronaldo With Level 3.

what i want to do is when i select cristiano my output should be like that
(Cristiano / Ronaldo / Dos Santos / Aveiro)
the output should gives me Cristiano / his father /his grand Father/his grand grand Father)

and this is the query to create the table with hierarchical view

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tbl_ChartOfAccountsTree](
[Chart_ID] [int] NOT NULL,
[Chart_Name] [nvarchar](500) NULL,
[Account_Level] [int] NULL,
[ParentAccount] [int] NULL,
[Accoutn_Type] [nvarchar](150) NULL,
[Direction] [nvarchar](150) NULL,
[OB] [decimal](18, 2) NULL,
[Date] [date] NULL,
CONSTRAINT [PK_Tbl_ChartOfAccountsTree] PRIMARY KEY CLUSTERED
(
[Chart_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT [dbo].[Tbl_ChartOfAccountsTree] ([Chart_ID], [Chart_Name], [Account_Level], [ParentAccount], [Accoutn_Type], [Direction], [OB], [Date]) VALUES (0, N'Aveiro', 0, NULL, N'Parent', NULL, CAST(0.00 AS Decimal(18, 2)), CAST(N'2019-07-01' AS Date))
GO
INSERT [dbo].[Tbl_ChartOfAccountsTree] ([Chart_ID], [Chart_Name], [Account_Level], [ParentAccount], [Accoutn_Type], [Direction], [OB], [Date]) VALUES (1000, N'Dos Santos', 1, 0, N'Parent', N'Balance', CAST(0.00 AS Decimal(18, 2)), CAST(N'2019-07-01' AS Date))
GO
INSERT [dbo].[Tbl_ChartOfAccountsTree] ([Chart_ID], [Chart_Name], [Account_Level], [ParentAccount], [Accoutn_Type], [Direction], [OB], [Date]) VALUES (1100, N'Ronaldo', 2, 1000, N'Parent', N'Balance', CAST(0.00 AS Decimal(18, 2)), CAST(N'2019-07-01' AS Date))
GO
INSERT [dbo].[Tbl_ChartOfAccountsTree] ([Chart_ID], [Chart_Name], [Account_Level], [ParentAccount], [Accoutn_Type], [Direction], [OB], [Date]) VALUES (1101, N'Cristiano', 3, 1100, N'Child', N'Balance', CAST(0.00 AS Decimal(18, 2)), CAST(N'2019-11-01' AS Date))
GO
ALTER TABLE [dbo].[Tbl_ChartOfAccountsTree] WITH CHECK ADD CONSTRAINT [FK_Tbl_ChartOfAccountsTree_Tbl_ChartOfAccountsTree] FOREIGN KEY([ParentAccount])
REFERENCES [dbo].[Tbl_ChartOfAccountsTree] ([Chart_ID])
GO
ALTER TABLE [dbo].[Tbl_ChartOfAccountsTree] CHECK CONSTRAINT [FK_Tbl_ChartOfAccountsTree_Tbl_ChartOfAccountsTree]
GO
so please is there is any possible help

Abuamer 29-Dec-19 3:31am

OK I will Give You An Example For What I want In More Details. Cristiano Ronaldo Is Agreat Player And His Full Name Is (Cristaino Ronaldo Dos Santos Aveiro).
Aveiro Is the grand Grand Father with level 0.
Dos Santos is the son Of Aveiro with level 1.
Ronaldo Is the son of Dos Santos with level 2.
And Finally Cristiano Is The Son of Ronaldo With Level 3.

what i want to do is when i select cristiano my output should be like that
(Cristiano / Ronaldo / Dos Santos / Aveiro)
the output should gives me Cristiano / his father /his grand Father/his grand grand Father)

and this is the query to create the table with hierarchical view

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tbl_ChartOfAccountsTree](
[Chart_ID] [int] NOT NULL,
[Chart_Name] [nvarchar](500) NULL,
[Account_Level] [int] NULL,
[ParentAccount] [int] NULL,
[Accoutn_Type] [nvarchar](150) NULL,
[Direction] [nvarchar](150) NULL,
[OB] [decimal](18, 2) NULL,
[Date] [date] NULL,
CONSTRAINT [PK_Tbl_ChartOfAccountsTree] PRIMARY KEY CLUSTERED
(
[Chart_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT [dbo].[Tbl_ChartOfAccountsTree] ([Chart_ID], [Chart_Name], [Account_Level], [ParentAccount], [Accoutn_Type], [Direction], [OB], [Date]) VALUES (0, N'Aveiro', 0, NULL, N'Parent', NULL, CAST(0.00 AS Decimal(18, 2)), CAST(N'2019-07-01' AS Date))
GO
INSERT [dbo].[Tbl_ChartOfAccountsTree] ([Chart_ID], [Chart_Name], [Account_Level], [ParentAccount], [Accoutn_Type], [Direction], [OB], [Date]) VALUES (1000, N'Dos Santos', 1, 0, N'Parent', N'Balance', CAST(0.00 AS Decimal(18, 2)), CAST(N'2019-07-01' AS Date))
GO
INSERT [dbo].[Tbl_ChartOfAccountsTree] ([Chart_ID], [Chart_Name], [Account_Level], [ParentAccount], [Accoutn_Type], [Direction], [OB], [Date]) VALUES (1100, N'Ronaldo', 2, 1000, N'Parent', N'Balance', CAST(0.00 AS Decimal(18, 2)), CAST(N'2019-07-01' AS Date))
GO
INSERT [dbo].[Tbl_ChartOfAccountsTree] ([Chart_ID], [Chart_Name], [Account_Level], [ParentAccount], [Accoutn_Type], [Direction], [OB], [Date]) VALUES (1101, N'Cristiano', 3, 1100, N'Child', N'Balance', CAST(0.00 AS Decimal(18, 2)), CAST(N'2019-11-01' AS Date))
GO
ALTER TABLE [dbo].[Tbl_ChartOfAccountsTree] WITH CHECK ADD CONSTRAINT [FK_Tbl_ChartOfAccountsTree_Tbl_ChartOfAccountsTree] FOREIGN KEY([ParentAccount])
REFERENCES [dbo].[Tbl_ChartOfAccountsTree] ([Chart_ID])
GO
ALTER TABLE [dbo].[Tbl_ChartOfAccountsTree] CHECK CONSTRAINT [FK_Tbl_ChartOfAccountsTree_Tbl_ChartOfAccountsTree]
GO
so please is there is any possible help

Wendelius 29-Dec-19 3:50am

I tried to make another example based on the names regarding to Cristiano Ronaldo. See the updated answer, hope that helps.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ahmed Elbaz · Accepted Answer · 2019-12-28T23:36:00

Based on the table definition and data you provided I recommend to create a function to return the fully qualified name (full name) of each account as follows:

SQL

CREATE FUNCTION [dbo].[GetAccountFullName](@AccountId INT) RETURNS NVARCHAR(MAX)
AS 
BEGIN

DECLARE @AccountFullName  NVARCHAR(MAX);

WITH HierarchyList_CTE
AS
(
SELECT    Chart_ID, Chart_Name, ParentAccount
FROM      Tbl_ChartOfAccountsTree
WHERE     Chart_ID = @AccountId
UNION ALL
SELECT         ST.Chart_ID, ST.Chart_Name, ST.ParentAccount
FROM          Tbl_ChartOfAccountsTree ST
INNER JOIN    HierarchyList_CTE CTE
              ON ST.Chart_ID = CTE.ParentAccount
)

SELECT @AccountFullName = COALESCE(@AccountFullName+'/','') + ISNULL(Chart_Name, '/')
FROM HierarchyList_CTE
 
RETURN @AccountFullName

END

The above function takes an account Id in this case Chart_ID and return its full name.
You can use it to return the full name for one account as follows:

SQL

SELECT [dbo].[GetAccountFullName] (1102)

and the result is

Lands/FixedAssets/Assets/MainTree

Or return full name for all records as follows:

SQL

SELECT    Chart_ID, Chart_Name, ParentAccount, [dbo].[GetAccountFullName] (Chart_ID) AS FullName
FROM      Tbl_ChartOfAccountsTree

and the result is

Chart_ID	Chart_Name	ParentAccount	FullName
0	    MainTree	NULL	MainTree
1000	Assets	0	Assets/MainTree
1100	FixedAssets	1000	FixedAssets/Assets/MainTree
1101	Buildings	1100	Buildings/FixedAssets/Assets/MainTree
1102	Lands	1100	Lands/FixedAssets/Assets/MainTree

Also, it is recommended to add a new column to the accounts table (Tbl_ChartOfAccountsTree) to store the full name value instead of generating it every time you need it as it is supposed to be infrequently changed.
To do that use the following code to add the column:

SQL

ALTER TABLE dbo.Tbl_ChartOfAccountsTree ADD FullName nvarchar(MAX) NULL

And then use the below code to update all accounts in the table:

SQL

UPDATE [dbo].[Tbl_ChartOfAccountsTree]
   SET [FullName] =  [dbo].[GetAccountFullName] (Chart_ID)

You might need to regenerate the full names whenever any change happened in the table. One way to do it is to add a trigger on the table to run the above update statement when any change happens as follows:

SQL

CREATE TRIGGER TriggerUpdateFullNames
   ON  [dbo].[Tbl_ChartOfAccountsTree]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN

	SET NOCOUNT ON;

	UPDATE [dbo].[Tbl_ChartOfAccountsTree]
    SET [FullName] =  [dbo].[GetAccountFullName] (Chart_ID)

END
GO

Now every time any change happened the full names will be automatically updated.

Hope this helps :)