none
Dynamic flattening of hierarchy in SQL Server RRS feed

  • Question

  •                                                                                                    

    We have a table LedgerAccount with 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:

    ledger_key |Ledger|Level0|Level1|Level2|Level3

    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




    Tuesday, September 15, 2020 7:39 AM

All replies

  • Hi Sweenal

    The purpose of this forum is to support the Open Specifications documentation. You can read about the Microsoft Open Specifications program from http://www.microsoft.com/openspecifications/en/us/default.aspx

    Good Luck on finding the answer your are looking from other forums.



    HungChun Yu (MSFT)

    Tuesday, September 15, 2020 11:11 AM