本文介紹了SQL Server中層次結(jié)構(gòu)的動(dòng)態(tài)扁平化的處理方法，對(duì)大家解決問(wèn)題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)吧！
問(wèn)題描述
我們有一個(gè)表LedgerAccount，其父子關(guān)系類(lèi)似于：
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定義層次結(jié)構(gòu)中的級(jí)別。父節(jié)點(diǎn)059999位于最高級(jí)別(在本例中為90)，并且0是最低級(jí)別的子節(jié)點(diǎn)。
我需要使用上表中的層次關(guān)系創(chuàng)建一個(gè)表/結(jié)構(gòu)/tmp.table，如下所示：
在這里，我們可以對(duì)級(jí)別的數(shù)量和級(jí)別ID進(jìn)行參數(shù)化。
以下是我嘗試過(guò)的查詢(xún)，沒(méi)有考慮參數(shù)化，并假定級(jí)別數(shù)=4。
如何才能在不硬編碼級(jí)別數(shù)量和級(jí)別ID的情況下實(shí)現(xiàn)相同的目標(biāo)？
我是一個(gè)SQL新手，對(duì)它有基本的了解。
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
轉(zhuǎn)到
推薦答案
這應(yīng)該可以滿(mǎn)足您的要求。
這里發(fā)生了很多事情，試圖將其分解成微小的細(xì)節(jié)將很快成為T(mén)LDR。因此，如果你有關(guān)于我為什么做某事或某事如何工作的具體問(wèn)題，請(qǐng)?jiān)谠u(píng)論中提問(wèn)，我會(huì)更新答案，包括那些具體的細(xì)節(jié)。
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
和結(jié)果…
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
這篇關(guān)于SQL Server中層次結(jié)構(gòu)的動(dòng)態(tài)扁平化的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，
