Recursive CTE and Ordering - 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)

Recursive CTE and Ordering

Author

Topic

leahsmart
Posting Yak Master

133 Posts

Posted - 2008-06-25 : 11:02:26

Hey,

I have been reading this article[url]http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/[/url] on Recursive CTE and have made some progress but I not sure how I go about getting the items in the desired order.

This is my table:

CREATE TABLE [dbo].[tblReportFolder](
	[ReportFolderID] [int] IDENTITY(1,1) NOT NULL,
	[ReportFolderName] [nvarchar](50) NOT NULL,
	[ReportFolderParentID] [int] NULL
)

Here is my rather random test data. Please note root folders are added with a [ReportFolderParentID] of NULL.


8	Colour	NULL
9	Red	8
10	Green	8
11	Blue	8
12	Yellow	8
14	Banana	2
1	Food	NULL
2	Fruit	1
3	Apple	2
4	Pear	2
5	Veg	1
6	Carrot	5
7	Potatoe	5

This is the code I have come up with for the Recursive CTE so far. It's working but the folders are not in the correct order. I am also not sure on my inner join to folders.

with Folders (r1, ReportFolderID,ReportFolderName,ReportFolderParentID)
as
(
select r1 = 1, ReportFolderID, ReportFolderName, ReportFolderParentID FROM tblReportFolder WHERE ReportFolderParentID IS NULL
union all
select r1 = r1 + 1, tblReportFolder.ReportFolderID, tblReportFolder.ReportFolderName, tblReportFolder.ReportFolderParentID FROM tblReportFolder INNER JOIN Folders ON Folders.ReportFolderID = tblReportFolder.ReportFolderParentID
)
select * from Folders

The following is desired folder order. I have indented the enteries to make it clearer for you.


8	Colour
11		Blue
10		Green
9		Red
12		Yellow
1	Food
2		Fruit
3			Apple
14			Banana
4			Pear
5		Veg
6			Carrot
7			Potatoe

Any help or links to articles regarding this would be greatly welcomed.

Thanks

Leah

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 11:10:30

[code]DECLARE @Sample TABLE (NodeID INT, NodeName VARCHAR(20), ParentNodeID INT)

INSERT @Sample
SELECT 1,'HR',0 UNION ALL
SELECT 2,'Finance',0 UNION ALL
SELECT 3,'Production',0 UNION ALL
SELECT 4,'Enterprise',3 UNION ALL
SELECT 5,'Mobile',3 UNION ALL
SELECT 6,'Telecom',3 UNION ALL
SELECT 7,'Towers',5 UNION ALL
SELECT 8,'Cables',5 UNION ALL
SELECT 9,'Multiplexers',8 UNION ALL
SELECT 10,'Pair Gain',8

;WITH Yak (NodeID, NodeName, ParentNodeID, NodePath, Indent)
AS (
SELECT NodeID,
NodeName,
ParentNodeID,
'/' + CAST(NodeID AS VARCHAR(MAX)),
0
FROM @Sample
WHERE ParentNodeID = 0

UNION ALL

SELECT s.NodeID,
s.NodeName,
s.ParentNodeID,
y.NodePath + '/' + CAST(s.NodeID AS VARCHAR(12)),
y.Indent + 1
FROM @Sample AS s
INNER JOIN Yak AS y ON y.NodeID = s.ParentNodeID
)

SELECT NodeID,
LEFT(REPLICATE(' ', Indent) + NodeName, 24) AS NodeName
FROM Yak
ORDER BY NodePath[/code]

E 12°55'05.25"
N 56°04'39.16"

leahsmart
Posting Yak Master

133 Posts

Posted - 2008-06-25 : 11:25:32

Perfect!

Thank you, it's soo easy and simple when someone gives you the answer! Feel a little stupid now...

with Folders (NodePath, r1, ReportFolderID,ReportFolderName,ReportFolderParentID)
as
(
select cast(ReportFolderName as varchar(max)), r1 = 1, ReportFolderID, ReportFolderName, ReportFolderParentID FROM tblReportFolder WHERE ReportFolderParentID IS NULL
union all
select cast(Folders.NodePath + tblReportFolder.ReportFolderName as varchar(max)), r1 = r1 + 1, tblReportFolder.ReportFolderID, tblReportFolder.ReportFolderName, tblReportFolder.ReportFolderParentID FROM tblReportFolder INNER JOIN Folders ON Folders.ReportFolderID = tblReportFolder.ReportFolderParentID
)
select * from Folders order by NodePath

Colour		1	8	Colour	NULL
ColourBlue	2	11	Blue	8
ColourGreen	2	10	Green	8
ColourRed	2	9	Red	8
ColourYellow	2	12	Yellow	8
Food		1	1	Food	NULL
FoodFruit	2	2	Fruit	1
FoodFruitApple	3	3	Apple	2
FoodFruitBanana	3	14	Banana	2
FoodFruitPear	3	4	Pear	2
FoodVeg		2	5	Veg	1
FoodVegCarrot	3	6	Carrot	5
FoodVegPotatoe	3	7	Potatoe	5

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources