SQL - recursive query

Posted on 2014-08-19
Last Modified: 2014-08-20
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

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.
Question by:Cubbybulin
    LVL 34

    Expert Comment

    by:Brian Crowe
    Please provide an example of the output you are expecting.  You are probably going to want to use a recursive common-table expression.
    LVL 34

    Accepted Solution

    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
    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
    	-- 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
    	-- 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


    Author Comment

    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

    Author Closing Comment

    This is what I needed. Thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now