TSQL 2005 query help please - SQL Server Forums

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)

TSQL 2005 query help please

Author

Topic

kkmurthy
Starting Member

41 Posts

Posted - 2009-06-24 : 11:33:02

I am pulling the data from the database table as follows:

Query start here:
WIth CTE(ID,projectname,ProjectLevelID,ParentID,OrganizationName,path) AS
(
SELECT ID, case
when ProjectLevelID=1 then ProjectName
when ProjectLevelID=2 then 'LVL2-'+ProjectName
when ProjectLevelID=3 then 'LVL3-'+ProjectName
end,
ProjectLevelID, parentID,OrganizationName,CAST(ID AS varchar(max))
FROM dbo.Project
WHERE parentID IS NULL
and IsDeleted=0
UNION ALL
SELECT t.ID, case
when t.ProjectLevelID=1 then t.ProjectName
when t.ProjectLevelID=2 then 'LVL2-'+t.ProjectName
when t.ProjectLevelID=3 then 'LVL3-'+t.ProjectName
end,
t.ProjectlevelID, t.parentID,t.OrganizationName,c.path+','+CAST(t.ID as varchar(10))
FROM dbo.Project 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
Query end here:

In the above query what is happening is:the data displays
level1 project first followed Level 2 project which is correct.
The problem I have is when the level 3 projects are displayed The level 3 Project does not follow Level2 immedietely.
The sample data currently displayed is as follows:

259 Project1 1 NULL dept 259
260 LVL2-Proj1 2 259 FCS 259,260
289 LVL2-proj2 2 259 FCS 259,289
261 LVL3-proj1 3 260 FCS 259,260,261
262 LVL3-proj2 3 260 FCS 259,260,262
263 LVL3-proj3 3 260 FCS 259,260,263
264 LVL3-proj4 3 260 FCS 259,260,264
265 LVL3-proj5 3 260 FCS 259,260,265
290 LVL3-proj6 3 260 FCS 259,260,290
291 LVL3-proj7 3 260 FCS 259,260,291

In the above case what I want is :
259 Project1 1 NULL dept 259
260 LVL2-Proj1 2 259 FCS 259,260
261 LVL3-proj1 3 260 FCS 259,260,261
262 LVL3-proj2 3 260 FCS 259,260,262
263 LVL3-proj3 3 260 FCS 259,260,263
264 LVL3-proj4 3 260 FCS 259,260,264
265 LVL3-proj5 3 260 FCS 259,260,265
290 LVL3-proj6 3 260 FCS 259,260,290
291 LVL3-proj7 3 260 FCS 259,260,291
289 LVL2-proj2 2 259 FCS 259,289

Can some one help me on this please.
KKM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-24 : 13:04:49

seems like ORDER BY should be as below

ORDER BY
LEFT(path,CASE WHEN CHARINDEX(',',path)>0 THEN CHARINDEX(',',path) ELSE LEN(path)+1 END-1)*1,
ID

kkmurthy
Starting Member

41 Posts

Posted - 2009-06-24 : 14:15:36

Unfortunately ID's doesn't work since ID s are seq numbers which gets generated as the user enters the data in front end. In this case if we sort it by ID's the data appears as below:
259 Project1 1 NULL dept 259
260 LVL2-Proj1 2 259 FCS 259,260
261 LVL3-proj1 3 260 FCS 259,260,261
262 LVL3-proj2 3 260 FCS 259,260,262
263 LVL3-proj3 3 260 FCS 259,260,263
264 LVL3-proj4 3 260 FCS 259,260,264
265 LVL3-proj5 3 260 FCS 259,260,265
289 LVL2-proj2 2 259 FCS 259,289
290 LVL3-proj6 3 260 FCS 259,260,290
291 LVL3-proj7 3 260 FCS 259,260,291

As you can see above the level 2 ID 289 appears before 290 and 291

Is there any other approach please ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources