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.
CubbybulinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
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 CroweDatabase AdministratorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.