Displaying directory structure directorywise

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Displaying directory structure directorywise

Author

Topic

amitranjan
Starting Member

45 Posts

Posted - 2010-07-25 : 22:32:32

I have two tables. One is parent and another is children.
Below is Schema and Data of Parent.


ID	RootDirectory
1	ah
2	20100714
3	ahcmsmp.fof
4	00050355.bat
5	00050356.bat
6	00050357.bat
7	ahcmsnsp.fof
8	00050347.bat
9	ahcmssp.fof
10	00050346.bat

Below is schema of children


ID	ParentDirectoryID	ChildrenDirectoryName
1	1	                 20100714
2	2	                 ahcmsmp.fof
3	3	                 00050355.bat
4	4	                 00001.tif
5	4	                 00002.tif
6	4	                 00050356.bat
7	5	                 00001.tif
8	5	                 00002.tif
9	5	                 00050357.bat
10	6	                 00001.tif

these two tables hold my directory which is as follows ah ->20100714->ahcmsmp.fof->00050537.bat->1.tif. So i need a query such that i can display ah as root group then 20100714 as next group then .fof folders as next group then .bat folders as next group and finally the count of tif in each .bat folders.

Please not AH root dir can have multiple directories , then child of AH that is 20100714 can also have multiple .fof directories and .fof can have multiple .bat directories


AH               20100714              ABC.FOF              1.BAT      33
                                                            2.BAT      44
                                                            3.BAT      45

                                       DEF.FOF              1.BAT       1

I want above as out put. so ineed query for the same

amit Ranjan

amit Ranjan

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-26 : 01:26:25

either something is wrong with your data or with your schema. i can't get such hierarchy as you are describing below from your sample data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources