Recursive CTE to show structure

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 to show structure

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-05-30 : 05:20:06

I have the following code that uses a recursive CTE to show parent/child structures. The results are okay, but don't really provide what I need. I was wondering if it was possible to amend the query below to show results in the following format which better represents the data?


SubjectName        Level
-------------------------
ASP.NET               0
  GridViews           1
     Paging           2
  DataSets            1
  DataTables          1
SQL Server            0
  CTE's               1
  Table Variables     1


DECLARE @tmp TABLE 
	(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[parentID] [int] NULL,
	[SubjectName] [nvarchar](200) NOT NULL
	);

INSERT INTO @tmp (parentID, SubjectName) VALUES (NULL, 'ASP.NET');
INSERT INTO @tmp (parentID, SubjectName) VALUES (NULL, 'SQL Server');
INSERT INTO @tmp (parentID, SubjectName) VALUES (1, 'GridViews');
INSERT INTO @tmp (parentID, SubjectName) VALUES (1, 'DataSets');
INSERT INTO @tmp (parentID, SubjectName) VALUES (1, 'DataTables');
INSERT INTO @tmp (parentID, SubjectName) VALUES (2, 'CTEs');
INSERT INTO @tmp (parentID, SubjectName) VALUES (2, 'Table Variables');
INSERT INTO @tmp (parentID, SubjectName) VALUES (3, 'Paging');

WITH Topics (ID, ParentID, SubjectName, Level)
AS
(
SELECT
	e.ID,
	e.ParentID,
	e.SubjectName,
	0 AS Level
FROM
	@tmp AS e
WHERE
	ParentID IS NULL
UNION ALL
SELECT
	e.ID,
	e.ParentID,
	e.SubjectName, 
	Level + 1
FROM
	@tmp AS e INNER JOIN
	Topics AS t ON e.ParentID = t.ID
)
SELECT 
	ID,
	ParentID,
	SubjectName,
	Level
FROM
	Topics

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-05-30 : 05:59:06

You need to introduce a new column in your CTE which I've called "TreePath":

WITH Topics (ID, ParentID, SubjectName, Level, TreePath) AS (
	SELECT
		e.ID,
		e.ParentID,
		e.SubjectName,
		0 AS Level,
		CAST('/' + CAST(e.ID as varchar) + '/' AS varchar(max)) AS TreePath
	FROM @tmp AS e
	WHERE ParentID IS NULL

	UNION ALL

	SELECT
		e.ID,
		e.ParentID,
		e.SubjectName, 
		Level + 1,
		TreePath = t.TreePath + CAST(e.ID AS varchar(200)) + '/'
	FROM @tmp AS e 
		INNER JOIN Topics AS t 
			ON e.ParentID = t.ID
)

SELECT 
	ID,
	ParentID,
	SPACE(Level * 4) + SubjectName,
	Level,
	TreePath
FROM Topics
ORDER BY TreePath

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-05-30 : 06:08:50

Absolutely fantastic! EXACTLY what I was looking for!

Thank you!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-05-30 : 06:10:19

Excellent :)

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-05-30 : 08:00:48

Sorry to be a pain. I wondered if it was possible to tweak your code slightly. If I have the following in the results...

ID ParentIDSubjectName Level TreePath
------------------------------------------------
1 NULL ASP.NET 0 /1/
5 1 FileUpload 1 /1/5/
7 5 Test 2 /1/5/7/
8 5 Alpha 2 /1/5/8/

Is it possible to order the child path nodes alphabetically, e.g. so Alpha would come before Test, but both still shown under their parent node of FileUpload???

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-05-30 : 08:28:23

No worries :)

There might be better ways to pull this off but this is the first that came to mind:

WITH Topics (ID, ParentID, SubjectName, Level, TreePath, TreePathText) AS (
	SELECT
		e.ID,
		e.ParentID,
		e.SubjectName,
		0 AS Level,
		CAST('/' + CAST(e.ID as varchar) + '/' AS varchar(max)) AS TreePath,
		CAST('/' + e.SubjectName + '/' AS varchar(max)) AS TreePathText
	FROM @tmp AS e
	WHERE ParentID IS NULL

	UNION ALL

	SELECT 
		e.ID,
		e.ParentID,
		e.SubjectName, 
		Level + 1,
		TreePath = t.TreePath + CAST(e.ID AS varchar(200)) + '/',
		TreePathText = t.TreePathText + CAST(e.SubjectName as varchar(200)) + '/'
	FROM @tmp AS e 
		INNER JOIN Topics AS t 
			ON e.ParentID = t.ID
)

SELECT 
	ID,
	ParentID,
	SPACE(Level * 4) + SubjectName,
	Level,
	TreePath,
	TreePathText
FROM Topics
ORDER BY TreePathText

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-05-31 : 13:20:16

Hahaa brilliant. Thank you very much (again!)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources