Organising a CTE Query - 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 2008 Forums

Transact-SQL (2008)

Organising a CTE Query

Author

Topic

graff101
Starting Member

4 Posts

Posted - 2009-04-02 : 11:30:30

Hi I have read many posts and articles on recursive/tree queeries and I am struggling to order my reults.

I have a table with a recursive join (sectionID & parentId) that I would like to query in order to build a tree structure of links.

Table SECTION

sectionID...title........displayOrder.....parentID
1.............About......1...................NULL
2.............Vessels....2...................NULL
3.............Outro......3...................NULL
4.............Tanker.....1...................2
5.............Offshore...2...................2

Query

WITH CTE1 AS (
SELECT distinct sectionID,parentID, title,displayOrder, 1 AS Level
FROM SECTION
where parentID is null

UNION ALL
SELECT E.sectionID, E.parentID,E.title, E.displayOrder, C.Level + 1
FROM SECTION E

INNER JOIN CTE1 C
ON E.parentID = C.sectionID
WHERE E.parentID is not NULL
)
SELECT title FROM CTE1
ORDER BY Level, displayOrder

The result returned looks like this...

About
Vessels
Outro
-Tanker
-Offshore

But I would like my query result to look like

About
Vessels
-Tanker
-Offshore
Outro

I'm not very expereinced in SQL like this and although I've managed to adapt certain tutorials to get this far my understanding of what actually is happening is very limited so any help would be greatly appreciated.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-05 : 21:25:42

try


WITH CTE1 AS (
SELECT distinct sectionID,parentID, title,displayOrder, 1 AS Level 
FROM SECTION
where parentID is null

UNION ALL
SELECT E.sectionID, E.parentID,E.title, E.displayOrder, C.Level + 1
FROM SECTION E

INNER JOIN CTE1 C
ON E.parentID = C.sectionID 
WHERE E.parentID is not NULL
)
SELECT title FROM CTE1 
ORDER BY Level, coalesce(ParentID,displayorder),displayOrder

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

graff101
Starting Member

4 Posts

Posted - 2009-04-14 : 10:42:16

Hi Thank yu for your reply. Unfortunately this only works if the sectionID's are in a sequential order. My result needs to work by listing all parents and children in the order defined by the displayOrder column.

I have found this query but it doesn't work for grandchildren.

WITH DirectReports (parentID, sectionID, title, alias, displayOrder, Level, Path)
AS
(
SELECT
parentID, sectionID, title, alias, displayOrder, 0 AS Level, CAST(sectionID AS VARCHAR(MAX)) AS Path
FROM
SECTION
WHERE
(parentID IS NULL) AND (presentationID = @presentationID)

UNION ALL

SELECT
e.parentID, e.sectionID, e.title, e.alias, e.displayOrder, Level + 1, Path + '/'+ CAST(Level + 1 AS VARCHAR(MAX))
FROM
SECTION AS e
JOIN
DirectReports AS d
ON
e.parentID = d.sectionID
)
SELECT sectionID , parentID ,title, alias ,Level,Path ,displayOrder
FROM DirectReports
ORDER BY Path, displayOrder asc

Anyone got any ideas how to format these results so that I can then produce this dynamic re-structuring for my site?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources