How to Fetch the Data Recursivly with Prior to Parent in Flat View
-
hi
i have Design the Table To maintain Folder Data in Hierachical Fashion .The Below is the Table Structure with sample Data
DECLARE @tblHierarchy TABLE
(
ID BIGINT,
AssetName NVARCHAR(50),
ParentID bigint,
IsFolder BIT, -- 1 Folder ,0- Normal Content
Sequence DECIMAL(18,2)
)
INSERT INTO @tblHierarchy
SELECT 1,'Cont 1',null,0,1 union all
SELECT 2,'Folder 1',null,1,2 union all
SELECT 3,'Folder 2',null,1,3 union all
SELECT 4,'Folder 3',null,1,4 union all
SELECT 102,'Botics',2,0,1 union all
SELECT 200,'Folder 1.1',2,1,2 union all
SELECT 189,'Neural EE',2,0,3 union all
SELECT 2300,'Folder 1.1.1',200,1,1 union all
SELECT 1001,'Cont Chemistry',null,0,5 Union all
SELECT 3,'Folder 4',null,1,6 union all
SELECT 789,'ContPostTest',2300,0,1 union all
SELECT 799,'ContPreTest ',2300,0,2
SELECT * FROM @tblHierarchy
Now the Problem is i need to give The Data in this Format
Using CTE we can Fetch the Data But i need to give data in sequnce which i mentioned above
Thanks In advance
Deepak.A
-
Deepak, the desired output image is not visible.. can u put the image as attachment?
-
Deepak, here is piece that might get you what you want:
This code will produce the heirarchical output when IsFolder column is not taken into account
;WITH HierarchyCTE (ID, AssetName , ParentID)AS
(
SELECT ID, AssetName , ParentID
FROM @tblHierarchy
WHERE ParentID IS NULL
UNION ALL
SELECT H.ID, H.AssetName , H.ParentID
FROM @tblHierarchy H
INNER JOIN HierarchyCTE CTE ON H.ParentID = CTE.ID
)
SELECT CTE.ID,
CTE.AssetName,
CASE WHEN H.AssetName IS NULL THEN 'Root Folder'
ELSE H.AssetName
END AS ParentFolder
FROM HierarchyCTE CTE
LEFT JOIN @tblHierarchy H ON H.ID = CTE.ParentID
ORDER BY ID
OPTION(MAXRECURSION 20)
This code will produce the heirarchical output when IsFolder column is taken into account
;WITH HierarchyCTE (ID, AssetName , ParentID)AS
(
SELECT ID, AssetName , ParentID
FROM @tblHierarchy
WHERE ParentID IS NULL AND IsFolder = 1
UNION ALL
SELECT H.ID, H.AssetName , H.ParentID
FROM @tblHierarchy H
INNER JOIN HierarchyCTE CTE ON H.ParentID = CTE.ID
)
SELECT CTE.ID,
CTE.AssetName,
CASE WHEN H.AssetName IS NULL THEN 'Root Folder'
ELSE H.AssetName
END AS ParentFolder
FROM HierarchyCTE CTE
LEFT JOIN @tblHierarchy H ON H.ID = CTE.ParentID
ORDER BY ID
Hope this helps! Tell us back if this fitted ur requirement!
CHeers!
-
Hi
Thanks the Quick reply
It is not meeting Reuirement
I will Attach the File Please Find the Attachment ForumData.xlsx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply