Click here to Skip to main content
15,793,452 members
Please Sign up or sign in to vote.
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
Updated 29-Dec-19 0:36am
v3

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
 
Share this answer
 
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.
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 :)
 
Share this answer
 
v3

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900