Dynamic flattening of hierarchy in SQL Server

Question

We have a table LedgerAccount with the parent-child relationship as:

 - CREATE TABLE [dbo].[LedgerAccounts](  
 - [ledger_key] [int] NOT NULL,  
 - [Ledger] [nvarchar](12) NULL,  
 - [LedgerLevel] [int] NULL,  
 - [ParentAccount] [nvarchar](12) NULL,  
 - [LedgerDescription] [nvarchar](30) NULL,  
 -      CONSTRAINT [PK_LedgerAccount] PRIMARY KEY CLUSTERED   
 -     (  
 - [ledger_key] ASC  
 -     )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
 -     ) ON [PRIMARY]  
 -   
 -   
 -     INSERT INTO [dbo].[LedgerAccounts]  
 -     VALUES (40, '020000', 0, '020999', 'Participation'),  
 - (41, '020999', 20, '021000', 'Participation in Group'),  
 - (42, '021000', 0, '021999', 'Loans to..'),  
 - (43, '021999', 20, '022000', 'Loans to group company'),  
 - (44, '022000', 0, '022999', 'Participation in'),  
 - (45, '022999', 20, '029999', 'Other Participation'),  
 - (46, '029999', 30, '059999', 'Financial Fixed Assets'),  
 - (47, '059999', 50, 'TOT.BALANS', 'Fixed Assets'),  
 - (48, 'TOT.BALANS', 90, 'TOT.GB', 'Total Balance sheet'),  
 - (49, 'TOT.GB', 99, 'NULL', 'Total GL')  

To create a table/view using the hierarchical relationship in the above table as:

The following query gives me the desired result but with static levels(for example let's say level 0 to 3).
How can I achieve the same without knowing the number of levels and levels id prior i.e. dynamically?
I am a newbie in SQL and have basic knowledge of it.

• create or alter view [dbo].[Ledgerview] as
  WITH LedgerAccountstree AS
  (
  SELECT
  ledger_key,
  Ledger as CurrLedgerCode,
  Ledger,
  Ledger as Lvl0Code,
  LedgerDescription as Lvl0Description,
  cast('-' as nvarchar(12)) as Lvl1Code,
  cast('-' as nvarchar(30)) as Lvl1Description,
  cast('-' as nvarchar(12)) as Lvl2Code,
  cast('-' as nvarchar(30)) as Lvl2Description,
  cast('-' as nvarchar(12)) as Lvl3Code,
  cast('-' as nvarchar(30)) as Lvl3Description,
  ParentAccount,
  LedgerLevel
  FROM
  [dbo].[LedgerAccounts]
  WHERE
  LedgerLevel = 50
  UNION ALL
  SELECT
  [dbo].[LedgerAccounts].ledger_key,
  LedgerAccountstree.CurrLedgerCode,
  [dbo].[LedgerAccounts].Ledger,
  LedgerAccountstree.Lvl0Code,
  LedgerAccountstree.Lvl0Description,
  case when [dbo].[LedgerAccounts].LedgerLevel = 30 then [dbo].[LedgerAccounts].Ledger else LedgerAccountstree.Lvl1Code end as Lvl1Code,
  case when [dbo].[LedgerAccounts].LedgerLevel = 30 then [dbo].[LedgerAccounts].LedgerDescription else LedgerAccountstree.Lvl1Description end as Lvl1Description,
  case when [dbo].[LedgerAccounts].LedgerLevel = 20 then [dbo].[LedgerAccounts].Ledger else LedgerAccountstree.Lvl2Code end as Lvl2Code,
  case when [dbo].[LedgerAccounts].LedgerLevel = 20 then [dbo].[LedgerAccounts].LedgerDescription else LedgerAccountstree.Lvl2Description end as Lvl2Description,
  case when [dbo].[LedgerAccounts].LedgerLevel = 0 then [dbo].[LedgerAccounts].Ledger else LedgerAccountstree.Lvl3Code end as Lvl3Code,
  case when [dbo].[LedgerAccounts].LedgerLevel = 0 then [dbo].[LedgerAccounts].LedgerDescription else LedgerAccountstree.Lvl3Description end as Lvl3Description,
  [dbo].[LedgerAccounts].ParentAccount,
  [dbo].[LedgerAccounts].LedgerLevel
  FROM
  [dbo].[LedgerAccounts]
  JOIN
  LedgerAccountstree
  ON LedgerAccountstree.Ledger = [dbo].[LedgerAccounts].[ParentAccount]
  )
  SELECT
  ledger_key,
  Ledger,
  Lvl3Code +'-'+ Lvl3Description as Level0,
  Lvl2Code +'-'+ Lvl2Description as Level1,
  Lvl1Code +'-'+ Lvl1Description as Level2,
  Lvl0Code +'-'+ Lvl0Description as Level3
  FROM
  LedgerAccountstree
  GO

Answer 1

Hi @Anonymous ,

I tried to understand the relationship in your table, but it was not easy. Based on your code, I probably understand what you want.But dynamic sql is not easy to achieve it.

Dynamic sql executes the query based on the input variable.LedgerLevel defines the level in the hierarchy.
In your query, LedgerLevel is known and needs to be divided into different levels according to it, which cannot be determined by input parameters. Unless you replace all LedgerLevel with multiple parameters, and then enter each parameter to return the result set, but this kind of dynamic SQL is meaningless, because your work will not be reduced and the query efficiency may not be as good as static sql.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.