Require sql of the given image

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)

Require sql of the given image

Author

Topic

amitranjan
Starting Member

45 Posts

Posted - 2010-07-19 : 00:55:03

Hi ineed to display directory structure in report as follows. My data in tables is as follows

and i want to display it on crystal report or excel soemthing like this. What will be the exact query

amit Ranjan

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-07-19 : 05:57:00

Your image is not clear, Can you post a large size image or the data directly.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

amitranjan
Starting Member

45 Posts

Posted - 2010-07-20 : 06:39:55

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources