本文介绍了SQL Server中层次结构的动态扁平化的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我们有一个表LedgerAccount，其父子关系类似于：
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定义层次结构中的级别。父节点059999位于最高级别(在本例中为90)，并且0是最低级别的子节点。 我需要使用上表中的层次关系创建一个表/结构/tmp.table，如下所示：
在这里，我们可以对级别的数量和级别ID进行参数化。 以下是我尝试过的查询，没有考虑参数化，并假定级别数=4。 如何才能在不硬编码级别数量和级别ID的情况下实现相同的目标？ 我是一个SQL新手，对它有基本的了解。
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
转到
推荐答案
这应该可以满足您的要求。 这里发生了很多事情，试图将其分解成微小的细节将很快成为TLDR。因此，如果你有关于我为什么做某事或某事如何工作的具体问题，请在评论中提问，我会更新答案，包括那些具体的细节。
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
和结果...
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
这篇关于SQL Server中层次结构的动态扁平化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！