MS SQL Hierarchy Concatenate all descendants into single value

Question

I have Googled / searched here for hours and cannot find anything that seems to end up with the result I desire, despite my best efforts.

I can't actually find an easy way to explain what I'm after so please consider the following example data:

Section_T

    SectionId   SectionParentId     S_Start S_End
    1           NULL                <A>     </A>
    2           NULL                <B>     </B>
    3           NULL                <C>     </C>
    4           NULL                <D>     </D>
    5           4                   <E>     </E>
    6           4                   <F>     </F>
    7           4                   <G>     </G>
    8           4                   <H>     </H>
    9           8                   <I>     </I>
    10          2                   <J>     </J>

and wish to get the following result:

Section
<A></A>
<B><J></J></B>
<C></C>
<D><E></E><F></F><G></G><H><I></I></H></D>

XML PATH doesn't seem to work for this scenario due to the HTML tag like data, which must stay.

The real data is a lot more complicated, but finding a solution to this, will point me in the right direction, and then I can go from there, as I'd rather figure out as much as I can on my own.

Upendra Chaudhari · Accepted Answer · 2014-02-20 13:23:06Z

1

You need to create recursive SQL function like below :

CREATE FUNCTION getSubNodes (@SectionId INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @SubNodes VARCHAR(MAX)
    SET @SubNodes = ''

    IF EXISTS(SELECT SectionId FROM Section_T WHERE SectionParentId = @SectionId)
    BEGIN
          SELECT   @SubNodes = COALESCE(@SubNodes + '','') + 
                   S_Start + dbo.getSubNodes(SectionId) + S_End 
          FROM Section_T 
          WHERE SectionParentId = @SectionId
          ORDER BY SectionOrder
    END

    RETURN @SubNodes
END

and you can use that function like below :

SELECT  S_Start + dbo.getSubNodes(SectionId) + S_End 
FROM Section_T
WHERE SectionParentId IS NULL

edited Feb 20, 2014 at 13:23

answered Feb 20, 2014 at 11:37

Upendra Chaudhari

6,5515 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

UltimateVenom Over a year ago

Using this method is there anyway to order (in there depth) the sub nodes by an order field of type INT (say SectionOrder)?

Upendra Chaudhari Over a year ago

Yes, you can add order by clause in function.

UltimateVenom Over a year ago

I did try that, adding it makes the function only return a single child.

Upendra Chaudhari Over a year ago

@UltimateVenom : Where you added order by clause ? Please check my updated answer with ORDER BY clause.

UltimateVenom Over a year ago

I simplified it, and it now works, I'm obviously missing something when I try to extend this, but I am not sure what yet... is there anything that would cause this behaviour that you can think of?

|

Collectives™ on Stack Overflow

MS SQL Hierarchy Concatenate all descendants into single value

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related