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_LedgerAccounts] 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')  

LedgerLevel defines the level in the hierarchy. The parent 059999 is on the highest level( in this example i.e. 90) and 0 is the lowest level child node.
I need to create a table/structure/tmp.table using the hierarchical relationship in the above table as follows :

Here, we can parameterize the number of levels and level ids.
Following is the query I have tried without considering the parameterization and assuming the number of levels = 4.
How can I achieve the same without hard-coding the number of levels and levels id?
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,  
		Lvl0Code +'-'+ Lvl0Description as Level0,  
		Lvl1Code +'-'+ Lvl1Description as Level1,   	  
    	Lvl2Code +'-'+ Lvl2Description as Level2,  
    	Lvl3Code +'-'+ Lvl3Description as Level3   
          
    FROM   
           LedgerAccountstree  
    	     
GO  

 

Answer 1

Hi @Anonymous ，

Please refer to:

 create PROCEDURE [dbo].[Ledgerview] (@LedgerLevel int)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,  
             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 = @LedgerLevel then [dbo].[LedgerAccounts].Ledger else LedgerAccountstree.Lvl1Code end as Lvl1Code,  
             case when [dbo].[LedgerAccounts].LedgerLevel = @LedgerLevel then [dbo].[LedgerAccounts].LedgerDescription else LedgerAccountstree.Lvl1Description end as Lvl1Description,  
             [dbo].[LedgerAccounts].ParentAccount,  
             [dbo].[LedgerAccounts].LedgerLevel  
         FROM   
             [dbo].[LedgerAccounts]  
         JOIN  
             LedgerAccountstree  
             ON LedgerAccountstree.Ledger = [dbo].[LedgerAccounts].[ParentAccount]  
     )  
    SELECT ledger_key,Ledger,Lvl1Code +'-'+ Lvl1Description as Level             
     FROM   
            LedgerAccountstree  
  
EXEC [dbo].[Ledgerview] @LedgerLevel = 30  
EXEC [dbo].[Ledgerview] @LedgerLevel = 20  
EXEC [dbo].[Ledgerview] @LedgerLevel = 0  
  
  
drop procedure [dbo].[Ledgerview]  

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