I'm trying to figure out a way to handle this hierarchy, but I'm not sure exactly how to. For sake of simplicity, I've made this example data:
CREATE TABLE #EXAMPLE (
ID INT
, PARENT_ID INT
, [PATH] VARCHAR(1000)
, [TYPE] VARCHAR(10)
);
INSERT INTO #EXAMPLE ([ID], PARENT_ID, [PATH], [TYPE]) VALUES
(63812, 0, '/Home', 'Folder')
,(55225, 63812, '/Home/User1', 'Folder')
,(92901, 63812, '/Home/User2', 'Folder')
,(40353, 63812, '/Home/User3', 'Folder')
,(96959, 55225, '/Home/User1/Item1', 'File')
,(97231, 55225, '/Home/User1/Item2', 'File')
,(53339, 92901, '/Home/User2/Item1', 'File')
,(58034, 92901, '/Home/User2/Music', 'Folder')
,(65023, 58034, '/Home/User2/Music/Item1', 'File')
,(72657, 58034, '/Home/User2/Music/Item2', 'File')
,(19406, 58034, '/Home/User2/Music/Item3', 'File')
,(56515, 58034, '/Home/User2/Music/Item4', 'File')
,(68394, 58034, '/Home/User2/Music/Item5', 'File')
,(42813, 92901, '/Home/User2/Movies', 'Folder')
,(32781, 42813, '/Home/User2/Movies/Item1', 'File')
,(96579, 40353, '/Home/User3/Scripts', 'Folder')
,(36300, 96579, '/Home/User3/Scripts/Item1', 'File')
,(59930, 96579, '/Home/User3/Scripts/SQL', 'Folder')
,(83700, 59930, '/Home/User3/Scripts/SQL/Item1', 'File')
,(66753, 59930, '/Home/User3/Scripts/SQL/Item2', 'File')
,(34377, 96579, '/Home/User3/Scripts/Other', 'Folder')
,(20666, 34377, '/Home/User3/Scripts/Other/Item1', 'File')
,(23786, 34377, '/Home/User3/Scripts/Other/Item2', 'File')
,(87107, 34377, '/Home/User3/Scripts/Other/Item3', 'File')
,(29557, 34377, '/Home/User3/Scripts/Other/Item4', 'File')
SELECT E.*
FROM #EXAMPLE AS E
DROP TABLE #EXAMPLE
It's a file path hierarchy, and what I want to do is ultimately make a report that will show, for instance, that there are 16 files under "/Home", and then you could drill-down to the next hierarchy level and see that "/Home/User1" has 2 files, "/Home/User2" has 7 files, and so forth.
I may be overthinking this... but to do so, I believe I have to show elements that are shared in common among the different file paths. At first, I tried something like this (after the table creation statement):
;WITH E_CTE AS (
SELECT E.ID, E.PARENT_ID, E.[PATH], E.[TYPE], [LVL] = 0--, [COMMON_ROOT] = SUBSTRING(E.[PATH], 0, 0)
FROM #EXAMPLE AS E
WHERE E.ID = 63812
UNION ALL
SELECT EXM.ID, EXM.PARENT_ID, EXM.[PATH], EXM.[TYPE], CTE.LVL 1--, SUBSTRING(EXM.[PATH], 0, CTE.LVL)
FROM E_CTE AS CTE
INNER JOIN #EXAMPLE AS EXM
ON CTE.ID = EXM.PARENT_ID
)
SELECT E.PARENT_ID, [PARENT_PATH] = ISNULL(EXM.[PATH], ''), E.ID, E.[PATH], E.[TYPE], E.LVL
FROM E_CTE AS E
LEFT JOIN #EXAMPLE AS EXM
ON E.PARENT_ID = EXM.ID
ORDER BY E.LVL, E.[PATH]
DROP TABLE #EXAMPLE
This gives me a dataset where I can see the immediate parent of a given record, but I can't see each parent element broken down. A hierarchy of PARENT_PATH and PATH makes it look like there are 9 parent groups, when I want it to show that there is one group at the top, "/Home", three groups at the second level, "/Home/User1", "/Home/User2", and "/Home/User3", and so forth. What I've arrived at is that I need a data set something like this:
CREATE TABLE #EXAMPLE (
ID INT
, PARENT_ID INT
, LEVEL_0 VARCHAR(1000)
, LEVEL_1 VARCHAR(1000)
, LEVEL_2 VARCHAR(1000)
, LEVEL_3 VARCHAR(1000)
, LEVEL_4 VARCHAR(1000)
, [PATH] VARCHAR(1000)
, [TYPE] VARCHAR(10)
);
INSERT INTO #EXAMPLE ([ID], PARENT_ID, LEVEL_0, LEVEL_1, LEVEL_2, LEVEL_3, LEVEL_4, [PATH], [TYPE]) VALUES
(63812, 0, '/Home', NULL, NULL, NULL, NULL, '/Home', 'Folder')
,(55225, 63812, '/Home', '/Home/User1', NULL, NULL, NULL, '/Home/User1', 'Folder')
,(92901, 63812, '/Home', '/Home/User2', NULL, NULL, NULL, '/Home/User2', 'Folder')
,(40353, 63812, '/Home', '/Home/User3', NULL, NULL, NULL, '/Home/User3', 'Folder')
,(96959, 55225, '/Home', '/Home/User1', '/Home/User1/Item1', NULL, NULL, '/Home/User1/Item1', 'File')
,(97231, 55225, '/Home', '/Home/User1', '/Home/User1/Item2', NULL, NULL, '/Home/User1/Item2', 'File')
,(53339, 92901, '/Home', '/Home/User2', '/Home/User2/Item1', NULL, NULL, '/Home/User2/Item1', 'File')
,(58034, 92901, '/Home', '/Home/User2', '/Home/User2/Music', NULL, NULL, '/Home/User2/Music', 'Folder')
,(65023, 58034, '/Home', '/Home/User2', '/Home/User2/Music', '/Home/User2/Music/Item1', NULL, '/Home/User2/Music/Item1', 'File')
,(72657, 58034, '/Home', '/Home/User2', '/Home/User2/Music', '/Home/User2/Music/Item2', NULL, '/Home/User2/Music/Item2', 'File')
,(19406, 58034, '/Home', '/Home/User2', '/Home/User2/Music', '/Home/User2/Music/Item3', NULL, '/Home/User2/Music/Item3', 'File')
,(56515, 58034, '/Home', '/Home/User2', '/Home/User2/Music', '/Home/User2/Music/Item4', NULL, '/Home/User2/Music/Item4', 'File')
,(68394, 58034, '/Home', '/Home/User2', '/Home/User2/Music', '/Home/User2/Music/Item5', NULL, '/Home/User2/Music/Item5', 'File')
,(42813, 92901, '/Home', '/Home/User2', '/Home/User2/Movies', NULL, NULL, '/Home/User2/Movies', 'Folder')
,(32781, 42813, '/Home', '/Home/User2', '/Home/User2/Movies', '/Home/User2/Movies/Item1', NULL, '/Home/User2/Movies/Item1', 'File')
,(96579, 40353, '/Home', '/Home/User3', '/Home/User3/Scripts', NULL, NULL, '/Home/User3/Scripts', 'Folder')
,(36300, 96579, '/Home', '/Home/User3', '/Home/User3/Scripts', '/Home/User3/Scripts/Item1', NULL, '/Home/User3/Scripts/Item1', 'File')
,(59930, 96579, '/Home', '/Home/User3', '/Home/User3/Scripts', '/Home/User3/Scripts/SQL', NULL, '/Home/User3/Scripts/SQL', 'Folder')
,(83700, 59930, '/Home', '/Home/User3', '/Home/User3/Scripts', '/Home/User3/Scripts/SQL', '/Home/User3/Scripts/SQL/Item1', '/Home/User3/Scripts/SQL/Item1', 'File')
,(66753, 59930, '/Home', '/Home/User3', '/Home/User3/Scripts', '/Home/User3/Scripts/SQL', '/Home/User3/Scripts/SQL/Item2', '/Home/User3/Scripts/SQL/Item2', 'File')
,(34377, 96579, '/Home', '/Home/User3', '/Home/User3/Scripts', '/Home/User3/Scripts/Other', NULL, '/Home/User3/Scripts/Other', 'Folder')
,(20666, 34377, '/Home', '/Home/User3', '/Home/User3/Scripts', '/Home/User3/Scripts/Other', '/Home/User3/Scripts/Other/Item1', '/Home/User3/Scripts/Other/Item1', 'File')
,(23786, 34377, '/Home', '/Home/User3', '/Home/User3/Scripts', '/Home/User3/Scripts/Other', '/Home/User3/Scripts/Other/Item2', '/Home/User3/Scripts/Other/Item2', 'File')
,(87107, 34377, '/Home', '/Home/User3', '/Home/User3/Scripts', '/Home/User3/Scripts/Other', '/Home/User3/Scripts/Other/Item3', '/Home/User3/Scripts/Other/Item3', 'File')
,(29557, 34377, '/Home', '/Home/User3', '/Home/User3/Scripts', '/Home/User3/Scripts/Other', '/Home/User3/Scripts/Other/Item4', '/Home/User3/Scripts/Other/Item4', 'File')
SELECT * FROM #EXAMPLE
DROP TABLE #EXAMPLE
This dataset shows me each row-level item (folder or file), the path, but then the entire "shared path" at each level of the hierarchy. My question is, how do I get from my first dataset to this dataset, using T-SQL? (That is, how do I get to that dynamically?) It would involve a dynamic number of columns depending on how many levels there are (in the production data there's more like seven or eight levels).
A secondary question is this: is there an alternative way to arrange/tabulate/organize this data such that in a reporting tool like SSRS or Tableau, I could drill down from the top level down to the bottom level in the manner I've described?
Thanks to anyone who takes the time to look at this question.
CodePudding user response：
Through some investigation I figured out what I needed, which wasn't what I asked for (didn't have the vocabulary to know what exactly to ask for starting off). After looking at the link from Wise Owl (https://www.youtube.com/watch?v=CHbqIsw5X30&list=PLNIs-AWhQzcmEFHyxCRwA_gb29WOz5SJU&index=28) I also referenced T-SQL Querying by Itzik Ben-Gan (2015) pp. 778-786 and came up with this nested set solution:
DECLARE @root AS INT = 63812;
CREATE TABLE #EXAMPLE (
ID INT
, PARENT_ID INT
, [PATH] VARCHAR(1000)
, [TYPE] VARCHAR(10)
);
INSERT INTO #EXAMPLE ([ID], PARENT_ID, [PATH], [TYPE]) VALUES
(63812, 0, '/Home', 'Folder')
,(55225, 63812, '/Home/User1', 'Folder')
,(92901, 63812, '/Home/User2', 'Folder')
,(40353, 63812, '/Home/User3', 'Folder')
,(96959, 55225, '/Home/User1/Item1', 'File')
,(97231, 55225, '/Home/User1/Item2', 'File')
,(53339, 92901, '/Home/User2/Item1', 'File')
,(58034, 92901, '/Home/User2/Music', 'Folder')
,(65023, 58034, '/Home/User2/Music/Item1', 'File')
,(72657, 58034, '/Home/User2/Music/Item2', 'File')
,(19406, 58034, '/Home/User2/Music/Item3', 'File')
,(56515, 58034, '/Home/User2/Music/Item4', 'File')
,(68394, 58034, '/Home/User2/Music/Item5', 'File')
,(42813, 92901, '/Home/User2/Movies', 'Folder')
,(32781, 42813, '/Home/User2/Movies/Item1', 'File')
,(96579, 40353, '/Home/User3/Scripts', 'Folder')
,(36300, 96579, '/Home/User3/Scripts/Item1', 'File')
,(59930, 96579, '/Home/User3/Scripts/SQL', 'Folder')
,(83700, 59930, '/Home/User3/Scripts/SQL/Item1', 'File')
,(66753, 59930, '/Home/User3/Scripts/SQL/Item2', 'File')
,(34377, 96579, '/Home/User3/Scripts/Other', 'Folder')
,(20666, 34377, '/Home/User3/Scripts/Other/Item1', 'File')
,(23786, 34377, '/Home/User3/Scripts/Other/Item2', 'File')
,(87107, 34377, '/Home/User3/Scripts/Other/Item3', 'File')
,(29557, 34377, '/Home/User3/Scripts/Other/Item4', 'File');
WITH TwoNums AS (
SELECT n FROM (VALUES(1), (2)) AS D(n)
)
, SortPath AS (
SELECT EXM.ID, EXM.[PATH], EXM.[TYPE], TN.n, [LVL] = 0, [SORT_PATH] = CONVERT(VARBINARY(MAX), CONVERT(BINARY(4), TN.n))
FROM #EXAMPLE AS EXM
CROSS JOIN TwoNums AS TN
WHERE EXM.ID = @root
UNION ALL
SELECT E.ID, E.[PATH], E.[TYPE], TNS.n, SP.LVL 1, SP.SORT_PATH CONVERT(BINARY(4), (-1 ROW_NUMBER() OVER(PARTITION BY E.PARENT_ID
ORDER BY E.[PATH])) / 2 * 2 TNS.n)
FROM SortPath AS SP
INNER JOIN #EXAMPLE AS E
ON SP.n = 1
AND E.PARENT_ID = SP.ID
CROSS JOIN TwoNums AS TNS
)
, Sort AS (
SELECT SP.ID, SP.[PATH], SP.[TYPE], [SortVal] = ROW_NUMBER() OVER(ORDER BY SP.SORT_PATH)
FROM SortPath AS SP
)
, NestedSets AS (
SELECT SRT.ID, SRT.[PATH], SRT.[TYPE], [LFT] = MIN(SRT.SortVal), [RGT] = MAX(SRT.SortVal)
FROM Sort AS SRT
GROUP BY SRT.ID, SRT.[PATH], SRT.[TYPE]
)
SELECT NS.*, EXM.PARENT_ID
INTO #StructureNS
FROM NestedSets AS NS
INNER JOIN #EXAMPLE AS EXM
ON NS.ID = EXM.ID;
SELECT NS.PARENT_ID, NS.ID, NS.[PATH], NS.[TYPE]
, [CNT_FILES] =
(SELECT COUNT(DISTINCT CHLD.ID)
FROM #StructureNS AS PRNT
INNER JOIN #StructureNS AS CHLD
ON CHLD.LFT BETWEEN PRNT.LFT AND PRNT.RGT
WHERE CHLD.RGT - CHLD.LFT = 1
AND PRNT.[TYPE] = 'Folder'
AND CHLD.[TYPE] = 'File'
AND NS.ID = PRNT.ID
)
FROM #StructureNS AS NS
DROP TABLE #EXAMPLE, #StructureNS
What I was going after was how do perform various aggregations (such as a count of files) at each level of a given recursive hierarchy. The T-SQL code does that nicely, though it seems like it would be possible with SSRS and a "Recursive" argument. Either way, the SSRS Recursive advanced setting allows me to display the data in the manner I was looking for, which was to drill down from the root out through all the nodes.