help please -Query to sort the data

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)

help please -Query to sort the data

Author

Topic

kkmurthy
Starting Member

41 Posts

Posted - 2008-12-16 : 08:54:38

Hi
I have a data stored in project table with columns:
ID Projectname ProjectLevelID parentID
As the user enters throgh the fron end the example data is as follows:

ID prjectname ProjectlevelID parentID
1 Program1 1 Null
12 project1 2 1
14 Project2 2 1
15 Subproject1 3 12
17 Subproject2 3 12
18 subproject3 3 12
20 Subproject12 3 14
26 Subproject13 3 14
30 Program2 1 Null
etc

The question I have is as follows:
When I query the table I want to get the data in order of Program -its projects -its subprojects. For example I want get the data in the following order:
Program1
Project1
Subproject1
Subproject2
Subproject3
Project2
Subproject12
Subproject13
Program2 and so on

Can some one help me please to sort the data in the above order

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 10:19:05

[code];WIth CTE(ID,prjectname,ProjectLevelID,ParentID,path) AS
(
SELECT ID, prjectname, ProjectlevelID, parentID,CAST(ID AS varchar(max))
FROM Table
WHERE parentID IS NULL
UNION ALL
SELECT t.ID, t.prjectname, t.ProjectlevelID, t.parentID,c.path+','+CAST(t.ID as varchar(10))
FROM Table t
JOIN CTE c
ON c.ID=t.ParentID
)
SELECT *
FROM CTE
ORDER BY LEFT(path,CASE WHEN CHARINDEX(',',path)>0 THEN CHARINDEX(',',path) ELSE LEN(path)+1 END-1)*1, ProjectlevelID,ID
[/code]

kkmurthy
Starting Member

41 Posts

Posted - 2008-12-16 : 10:56:18

Thank you very much sir for the help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 10:59:35

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources