Hierarchial SQL Query

I am using SQL Server 2005, I have a single table called Docs with a single column开发者_开发百科 called Path.

Path column contains file paths:

"C:\MyDocs\1.txt"
"C:\MyDocs\Folder1\3.txt"

I need my query to retrieve in a single query how many children each folder and subfolder has.

In my example above I need the query to retrieve:

3 children (1 folder and 2 files) for "MyDocs" 1 child (1 file) for "Folder1".

Any ideas?

Thank you.

Add a column dir with directory path (ex. for "C:\MyDocs\Folder1\3.txt" it should contain "C:\MyDocs\Folder1\"). Then:

select dirs.path, count(*) from Docs files 
join (select distinct dir as path from Docs) dirs
    on (files.dir like dirs.path + '%')
group by dirs.path

I didn't run the query, but the idea should be clear.

You'll need to split the path into separate strings and normalize the data. Here's a previous question on splitting string in sql.

Once you've done that you can use a Recursive CTE to get the data.

You can solve this using string manipulation and, for clarity, two CTEs:

WITH
R1(RevPath) as (select reverse(Path) from Docs),
R2(ParentFolder) as
(select reverse( substring(RevPath, charindex('\', RevPath), 3333)) from R1)
select ParentFolder, count(*) from R2
group by ParentFolder
go

You might have to tweak this a little bit depending on your data. If your table lists only files, this query should be fine. If it lists also folders with a trailing backslash, subtract 1 from the count.

How to get a listing of all folders and files contained in them

WITH
R1(Path, RevPath) as (select Path, reverse(Path) from Docs),
R2(ParentFolder, Path) as
(select
  reverse( substring(RevPath, charindex('\', RevPath), 3333)),
  Path
 from R1)
select * from R2
where ParentFolder LIKE 'C:\some\folder\%' -- all folders below C:\some\folder
go

Not tested!