September 25, 2014 at 5:24 am
We have a hierarchical table of some 2-3k of rows that grows slowly at only 3-5 rows a month, and is never likely to be above 5k, holding file/directory names with an IdParent int value pointing at that items immediate parent or NULL if root
Below is a sample:
Id Name IdParent
1 C:\ NULL
2 D:\ NULL
3 ProgramFiles\ 1
4 DataFiles 2
5 Excel.EXE 3
6 MyDataList.CSV 4
I appreciate that the iterative code required to achieve the result I am looking for is best handled in the Application layer, but in this case the design brief is that we must get our results back as a View. Speed of execution and code clarity are secondary 'icing on the cake' issues.
I have tried recursive CTE's and XML FOR solutions but without success, and while I feel the latter is probably the best all rounder the searches I have made and the examples I have read do not seem to solve this particular issue.
Even to find the starting point of the search tree (i.e. the .EXE and the .CSV items) was difficult because the final slash is optional in some of the rows (.e. row 4)
Perhaps someone on the forum can assist me?
The end result of my view on the above data should return:
Id FilePath
5 C:\ProgramFiles\Excel.EXE
6 D:\DataFiles\MyDataList.CSV (note the adding of the missing delimiter between DataFiles and MyDataList)
When I first came across this requirement I felt that writing a View would be relatively straightforward, so it is frustrating to have to admit defeat and come on to the forum, but hopefully this is an issue that has been encountered and resolved by others before me
Thanks in advance.
September 25, 2014 at 6:33 am
I guess your interesting read would be Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets[/url] and the sequels 😉
just solving your simple example using a cte can be done this way:
DECLARE @ResultString varchar( 5000 ) = '';
;WITH cte
AS ( SELECT Id AS IdRoot
, Name
, IdParent
, 0 AS RecursionLevel
FROM @tbl
WHERE Name = 'MyDataList.CSV'
UNION ALL
SELECT Anchor.IdRoot
, T.Name
, T.IdParent
, Anchor.RecursionLevel - 1
FROM @tbl AS T
INNER JOIN cte AS Anchor
ON Anchor.IdParent = T.id )
SELECT @ResultString+=CASE RecursionLevel
WHEN 0 THEN Name
ELSE CASE
WHEN Name LIKE '%\' THEN Name
ELSE Name + '\'
END
END
FROM cte
ORDER BY IdRoot , RecursionLevel;
SELECT @ResultString AS ResultString;
Just keep in mind there is a recursion limit when using cte.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 25, 2014 at 8:40 am
Thanks for your prompt reply
My own probe into the issue also started with a recursive CTE (and no - the recursion limit is not a problem), but I quickly came across the snag that I was not identifying the start point in each table.
In the example given one could perhaps start the search on the assumption that each row with a name containing a period is the starting point for the traversing up(down?) the tree. But this period character cannot be assumed with 100% certainty.
So in effect the start points (in the example given above) are the two rows which do not have another row pointing to them, and of course the end point is the row that has a NULL
In the app the devTeam are using the user selects a file name, and so the search is simplified within the app, but in the View I need to create I need to find each end point and return each of their file paths as below:
Id FilePath
5 C:\ProgramFiles\Excel.EXE
6 D:\DataFiles\MyDataList.CSV
Having abandoned recursive Ctes as a possible solution I did start to look at the FOR XML as a possible solution, but can't get my head round the syntax to achieve the wanted result
I could live with an alternative more verbose result like:
1 C:2 D:3 C:\ProgramFiles4 D:\DataFiles5 C:\ProgramFiles\Excel.EXE
6 D:\DataFiles\MyDataList.CSV
I do hope you or a colleague in this great resource/community have faced and solved this or a similar problem, as the devTeam are now starting to rib me about how long such a 'simple' task is taking to do in SQL
:blush:
September 26, 2014 at 12:23 am
how about this:
/* still using the recursive CTE */
Create table MyTable ( Id int not null, Name varchar(128) not null, IdParent int NULL );
go
Create view V_X
as
WITH cte
AS ( SELECT Id AS IdRoot
, Name As RootName
, Name
, IdParent
, 0 AS RecursionLevel
FROM MyTable
WHERE Name like '%.%'
UNION ALL
SELECT Anchor.IdRoot
, Anchor.RootName
, T.Name
, T.IdParent
, Anchor.RecursionLevel - 1
FROM MyTable AS T
INNER JOIN cte AS Anchor
ON Anchor.IdParent = T.id )
SELECT P.IdRoot
, P.RootName
, Details.FullPathName
FROM cte P
CROSS APPLY (
SELECT ''+
CASE Ch.RecursionLevel
WHEN 0 THEN Ch.Name
ELSE CASE
WHEN Ch.Name LIKE '%\' THEN Name
ELSE Ch.Name + '\'
END
END /* Do not name this column or your XML will use it for tags */
FROM cte AS Ch
WHERE Ch.IdRoot = P.IdRoot
ORDER BY Ch.RecursionLevel
FOR
XML PATH('')
, TYPE
) AS Details ( FullPathName )
where P.IdParent is null
go
insert into MyTable
values (1 ,'C:\', NULL)
,(2 ,'D:\', NULL)
,(3 ,'ProgramFiles\', 1)
,(4 ,'DataFiles', 2)
,(5 ,'Excel.EXE', 3)
,(6 ,'MyDataList.CSV', 4)
,(7 ,'MyProgram', 3)
,(8 ,'MySubfolder\', 7)
,(9 ,'MyProgram.exe', 8)
;
Select *
from V_X
order by IdRoot ;
Find the weak spot !!
You still have to check Jeffs article I mention in my previous reply !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 26, 2014 at 2:43 am
Good morning Johan,
Thank you very much for providing me with what will definitely work for me now.
Can't claim to fully understand what is going on (Recursion never being a strong point for me), but when I get a few minutes will get my head round it all and come back
Also nice to see that you are using a CROSS APPLY and FOR XML construct which are on my priority reading list for further study. Fortunately I am on hols next week so being a sad geek type will probably sit on the beach improving the tan and reading up on your solution and the article you recommend.
I'm looking for the weak spot you mention and if it is the fact that you have short circuited the search starting point by looking out for a period in the file name then I can live with that for now, and perhaps even come back with a solution of my own (but don't wait up:-) )
Once again - thank you very much personally and to this forum also, which is such a great source of information and ideas
September 26, 2014 at 3:46 am
Spot on for the weak spot detection !
Enjoy your holiday !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply