• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

SQL - recursive query

Trying to create a recursive query on a sql table that has structure of...

serial, parent_serial, name, sortCol

Data like this...

0, null, Home, 0
1, 0, FirstChild, 0
2, 0, SecondChild, 1
3, 0, Thirdchild, 2
4, 1, FirstChild-Child, 0
5, 4, FirstChild-Child-Child, 0
6, 2, SecondChild-Child, 0
etc...

Need to create a table that shows the hierarchical rows based on the serial and parent_serial fields and also sorted by the sortCol field.

Have seen examples using Unions but not sure how to implement.
0
Cubbybulin
Asked:
Cubbybulin
  • 2
  • 2
1 Solution
 
Brian CroweCommented:
Please provide an example of the output you are expecting.  You are probably going to want to use a recursive common-table expression.
0
 
Brian CroweCommented:
Hopefully this will help...

CREATE TABLE #Serial
(
	Serial			INT IDENTITY(0,1) PRIMARY KEY,
	Parent_Serial	INT NULL,
	Name			VARCHAR(50) NULL,
	SortCol			INT NULL
);

INSERT INTO #Serial
(
	Parent_Serial,
    Name,
    SortCol
)
VALUES  (null, 'Home', 0),
	(0, 'FirstChild', 0),
	(0, 'SecondChild', 1),
	(0, 'Thirdchild', 2),
	(1, 'FirstChild-Child', 0),
	(4, 'FirstChild-Child-Child', 0),
	(2, 'SecondChild-Child', 0)

SELECT * FROM #Serial;

WITH cteSerial
AS
(
	-- Anchor member definition
	SELECT S.Parent_Serial, S.Serial, S.Name, S.SortCol, 0 AS Level
	FROM #Serial AS S
	WHERE Parent_Serial IS NULL
	UNION ALL
	-- Recursive member definition
	SELECT S.Parent_Serial, S.Serial, S.Name, S.SortCol, Parent.Level + 1
	FROM #Serial AS S
	INNER JOIN cteSerial AS Parent
		ON S.Parent_Serial = Parent.Serial	  
)
SELECT *
FROM cteSerial
ORDER BY Parent_Serial, Level, SortCol

Open in new window

0
 
CubbybulinAuthor Commented:
I am ultimately wanting to create a Tree on a web page using a <ul> based on the query.
I am thinking the table would look like below. As I would cycle thru each row I would add a new <ul> if the first field ID changed. If you have a better way to derive the tree based on the table that would be great. Hope that is what info you need.

Home,                       null
Home,                       Firstchild
Home,                       SecondChild
SecondChild,            First Child of SecondChild
SecondChild-Child, FirstChild of SecondChild-Child
SecondChild,           SecondChild of SecondChild
Home,                      ThirdChild
0
 
CubbybulinAuthor Commented:
This is what I needed. Thanks!
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now