sql-serverdynamicparent-childhierarchyflatten
Flattening of hierarchy in SQL Server Dynamically
We have a table LedgerAccount with parent-child relationship similar to:
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')
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
Solution
This should do what you're looking for. There is a lot going on here and trying to break it all down into minute details would would quickly become a TLDR. So, if you have specific questions about why I did something or how something works, ask it in the comments and I will update the answer to include those specific details.
USE tempdb;
GO
IF OBJECT_ID('tempdb.dbo.LedgerAccounts', 'U') IS NOT NULL
BEGIN DROP TABLE tempdb.dbo.LedgerAccounts; END;
GO
CREATE TABLE tempdb.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];
GO
INSERT INTO tempdb.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');
GO
-- SELECT * FROM tempdb.dbo.LedgerAccounts la;
--=====================================================================================================================
--=====================================================================================================================
IF OBJECT_ID('tempdb..#build_path', 'U') IS NOT NULL
BEGIN DROP TABLE #build_path; END;
GO
CREATE TABLE #build_path (
ledger_key int NOT NULL,
Ledger nvarchar(12) NOT NULL,
ParentAccount nvarchar(30) NOT NULL,
h_level int NOT NULL,
h_path nvarchar(4000) NOT NULL
);
GO
WITH
cte_build_path AS (
SELECT
la.ledger_key,
la.Ledger,
la.ParentAccount,
h_level = 0,
h_path = CONVERT(nvarchar(4000), RIGHT(REPLICATE(N' ', 50) + la.ledger + N'-' + la.LedgerDescription, 50))
FROM
dbo.LedgerAccounts la
WHERE
la.Ledger LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'
AND la.ParentAccount NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'
UNION ALL
SELECT
la.ledger_key,
la.Ledger,
la.ParentAccount,
h_level = bp.h_level + 1,
h_path = CONVERT(nvarchar(4000), bp.h_path + RIGHT(REPLICATE(N' ', 50) + la.ledger + N'-' + la.LedgerDescription, 50))
FROM
dbo.LedgerAccounts la
JOIN cte_build_path bp
ON la.ParentAccount = bp.Ledger
)
INSERT #build_path (ledger_key, Ledger, ParentAccount, h_level, h_path)
SELECT
bp .ledger_key,
bp.Ledger,
bp.ParentAccount,
bp.h_level,
bp.h_path
FROM
cte_build_path bp;
GO
-- SELECT * FROM #build_path bp
--=====================================================================================================================
DECLARE
@d_col_count int = (SELECT MAX(bp.h_level) FROM #build_path bp) + 1,
@d_sql nvarchar(MAX) = N'',
@debug bit = 0;
SELECT TOP (@d_col_count)
@d_sql = CONCAT(@d_sql, N',
[level', x.rn, N'] = CASE WHEN bp.h_level >= ', x.rn, N' THEN LTRIM(SUBSTRING(bp.h_path, ', x.rn * 50 + 1, N', 50)) ELSE N''---'' END'
)
FROM
(
SELECT TOP (@d_col_count)
rn = ROW_NUMBER() OVER (ORDER BY ac.object_id) - 1
FROM
sys.all_columns ac
) x
ORDER BY
x.rn ASC;
SELECT @d_sql = CONCAT(N'
SELECT
bp.ledger_key,
bp.Ledger',
@d_sql, N'
FROM
#build_path bp;');
IF @debug = 1
BEGIN
PRINT(@d_sql);
END;
ELSE
BEGIN
EXEC sys.sp_executesql @d_sql
END;
GO
And the results...
ledger_key Ledger level0 level1 level2 level3 level4 level5 level6 level7
----------- ------------ -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
47 059999 059999-Fixed Assets --- --- --- --- --- --- ---
46 029999 059999-Fixed Assets 029999-Financial Fixed Assets --- --- --- --- --- ---
45 022999 059999-Fixed Assets 029999-Financial Fixed Assets 022999-Other Participation --- --- --- --- ---
44 022000 059999-Fixed Assets 029999-Financial Fixed Assets 022999-Other Participation 022000-Participation in --- --- --- ---
43 021999 059999-Fixed Assets 029999-Financial Fixed Assets 022999-Other Participation 022000-Participation in 021999-Loans to group company --- --- ---
42 021000 059999-Fixed Assets 029999-Financial Fixed Assets 022999-Other Participation 022000-Participation in 021999-Loans to group company 021000-Loans to.. --- ---
41 020999 059999-Fixed Assets 029999-Financial Fixed Assets 022999-Other Participation 022000-Participation in 021999-Loans to group company 021000-Loans to.. 020999-Participation in Group ---
40 020000 059999-Fixed Assets 029999-Financial Fixed Assets 022999-Other Participation 022000-Participation in 021999-Loans to group company 021000-Loans to.. 020999-Participation in Group 020000-Participation
- Math.Sin() gives incorrect value
- How to run my python script when the sunOS is start booting
- Express-session: not resetting cookie expiration on each request
- Getting a stack overflow exception when normalizing a vector
- Edit default summary function in R gives error for multiple variables
- What was a For loop? Why isn't it needed in R?
- How to use download button in shiny and save results in various formats (csv, texte, pdf, spss...)?
- Why are there two assignment operators, `<-` and `->` in R?
- lm()$assign: what is it?
- How to get the value of list(...) in R and S functions
- Design matrix for MLM from library(lme4) with fixed and random effects
- how to generate elements not included in my sample
- Create a matrix with gradually changing values without a for loop
- Emacs ESS and S-plus ( S+ ) 8.1 compatability
- How to lag date-index in a time-series in R?
- Nonlinear regression in R / S
- Calling R from S-Plus?