1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gil
  • 109
  • 1
  • 10
  • 1
    This is not a job for a query. It would be far easier and more efficient to pull the subset of records you need, and do this type of processing in the application. The .Net framework's Directory class would make short work of this. – Britchie May 15 '11 at 13:52
  • Why this is not a job for a query? If you have thousand of directories and millions of files it would be insane to fetch all the paths and process them in the application. – mabn May 15 '11 at 14:11

3 Answers3

2

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.

mabn
  • 2,473
  • 1
  • 26
  • 47
1

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.

Community
  • 1
  • 1
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
0

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!

Lumi
  • 14,775
  • 8
  • 59
  • 92
  • Thanks Michael, How do I display also the files names in each folder and not only their count? – Gil May 16 '11 at 09:12