Reverse order of a CTE result?-CodePudding

Here's my code, to traverse all nodes starting from one:

SELECT * INTO MyTable
FROM
(
    SELECT 1 Id, 1 ParentId, 'Parent A' Name
    UNION ALL
    SELECT 5,1,'Child A1'
    UNION ALL
    SELECT 47894,5,'Child A2'
    UNION ALL
    SELECT 2,2, 'Parent B'
    UNION ALL
    SELECT 3,2, 'Child B1'
)TAB

;With CTE as
(
  select * from MyTable where Id = 47894
  union all
  select a.* from MyTable a inner join cte b 
    on a.Id=b.ParentId and a.Id<>b.Id
)

select STRING_AGG(Name, ' >> ') from CTE

With input 47894, it gives:

Child A2 >> Child A1 >> Parent A

What if I need the reverse?

Parent A >> Child A1 >> Child A2

CodePudding user response：

You can use this:

SELECT *
INTO   MyTable
FROM   (
          SELECT
             1 Id,
             1 ParentId,
             'Parent A' Name
          UNION ALL
          SELECT
             5,
             1,
             'Child A1'
          UNION ALL
          SELECT
             47894,
             5,
             'Child A2'
          UNION ALL
          SELECT
             2,
             2,
             'Parent B'
          UNION ALL
          SELECT
             3,
             2,
             'Child B1'
       ) TAB;
WITH CTE
AS (SELECT
          Id,
          ParentId,
          Name,
          CAST(1 AS INT) AS OrderByValue
    FROM  MyTable
    WHERE Id = 47894
    UNION ALL
    SELECT
         a.Id,
         a.ParentId,
         a.Name,
         OrderByValue   1
    FROM MyTable a
         INNER JOIN CTE b ON a.Id = b.ParentId
                             AND a.Id <> b.Id)
SELECT STRING_AGG(Name, ' >> ')WITHIN GROUP(ORDER BY CTE.OrderByValue DESC)
FROM   CTE;