Recursive SQL Query with order tree

Question

I have a problem to order a query as a tree as this

WITH UtHierarchy
AS (
    SELECT etabid
        ,ut
        ,utlib
        ,parenteut
        ,0 AS LEVEL
        ,ut AS root
    FROM RUT
    WHERE etabid = 1
        AND parenteut IS NULL

    UNION ALL

    SELECT RUT.etabid
        ,RUT.ut
        ,RUT.utlib
        ,RUT.parenteut
        ,LEVEL + 1 AS LEVEL
        ,RUT.parenteut AS root
    FROM RUT
    INNER JOIN UtHierarchy uh ON uh.ut = rut.parenteut
    WHERE RUT.ETABID = 1
    )
SELECT *
FROM UtHierarchy
ORDER BY root

I need to have the following tree :

UT Root
UT Root
-- UT level 1
UT Root
-- UT level 1
-- -- UT level 2
UT Root

This is working as intended for level 0 or 1, but for higher level it s broken. I try to select in root column the 'level 0' parent to order by root and ut, but after some time on this problem, I can't :(

How to resolve this ?

Thanks for your help.

EDIT : Thank you for editing with sql colors :) I've seen the solution for the topmost level but user has deleted his post.

WITH UtHierarchy 
AS (
  SELECT etabid
  ,ut
  ,utlib
  ,parenteut,
  0 as profondeur,
  ut as root
  FROM RUT 
  where etabid = 278
  and parenteut is null
  UNION  ALL
  SELECT RUT.etabid
  , RUT.ut
  , RUT.utlib
  , RUT.parenteut
  , profondeur + 1 as profondeur
  , root as root 
  FROM RUT 
  inner join UtHierarchy uh on uh.ut = rut.parenteut
  where RUT.ETABID = 278
)
select ut, parenteut, profondeur, root 
from UtHierarchy
order by root

but it's not working too

Here an example with true data

ut  parenteutlevel  root
10  1   1   1
11  1   1   1
12  1   1   1
13  1   1   1
14  1   1   1
130 13  2   1
131 13  2   1
132 13  2   1
133 13  2   1
134 13  2   1
135 13  2   1
136 13  2   1
120 12  2   1
121 12  2   1
122 12  2   1
110 11  2   1
111 11  2   1
112 11  2   1
113 11  2   1
114 11  2   1
115 11  2   1
116 11  2   1
101 10  2   1
102 10  2   1
103 10  2   1
104 10  2   1
105 10  2   1
106 10  2   1
107 10  2   1
1       0   1

As you can see it is not the good structure. I need a tree as this :

ut  parenteutlevel  root
1       0   1
10  1   1   1
101 10  2   1
102 10  2   1
103 10  2   1
104 10  2   1
105 10  2   1
106 10  2   1
107 10  2   1
11  1   1   1
110 11  2   1
111 11  2   1
112 11  2   1
113 11  2   1
114 11  2   1
115 11  2   1
116 11  2   1
12  1   1   1
120 12  2   1
121 12  2   1
122 12  2   1
13  1   1   1
130 13  2   1
131 13  2   1
132 13  2   1
133 13  2   1
134 13  2   1
135 13  2   1
136 13  2   1
14  1   1   1

You didn't tell us the problem.

usr
– usr

2012-04-04 14:44:28 +00:00
Commented Apr 4, 2012 at 14:44 — usr
– usr, Commented Apr 4, 2012 at 14:44

ANisus · Accepted Answer · 2014-09-03 22:50:38Z

10

ince the Recursive call is correct, your problem lies in the sorting of the result

ORDER BY root

You can try to create a sort path to help getting them in the right order:

WITH UtHierarchy
AS (
    SELECT etabid
        ,ut
        ,utlib
        ,parenteut
        ,0 AS LEVEL
        ,ut AS root
        ,RIGHT('000000' + CAST(ut AS varchar(MAX)), 6) AS sort
    FROM RUT
    WHERE etabid = 1
        AND parenteut IS NULL

    UNION ALL

    SELECT RUT.etabid
        ,RUT.ut
        ,RUT.utlib
        ,RUT.parenteut
        ,LEVEL + 1 AS LEVEL
        ,RUT.parenteut AS root
        ,uh.sort+'/'+RIGHT('000000' + CAST(RUT.ut AS varchar(20)), 6) AS sort
    FROM RUT
    INNER JOIN UtHierarchy uh ON uh.ut = rut.parenteut
    WHERE RUT.ETABID = 1
    )
SELECT *
FROM UtHierarchy
ORDER BY sort

Edit:

CASE => CAST (Spelling mistake)

Edit 2 (Adding a working example from your test data):

Here you have a copy and paste test code. Works fine for me:

SELECT 10 AS ut, 1 AS parenteut
INTO #RUT
UNION ALL SELECT 11, 1
UNION ALL SELECT 12, 1
UNION ALL SELECT 13, 1
UNION ALL SELECT 14, 1
UNION ALL SELECT 130, 13
UNION ALL SELECT 131, 13
UNION ALL SELECT 132, 13
UNION ALL SELECT 133, 13
UNION ALL SELECT 134, 13
UNION ALL SELECT 135, 13
UNION ALL SELECT 136, 13
UNION ALL SELECT 120, 12
UNION ALL SELECT 121, 12
UNION ALL SELECT 122, 12
UNION ALL SELECT 110, 11
UNION ALL SELECT 111, 11
UNION ALL SELECT 112, 11
UNION ALL SELECT 113, 11
UNION ALL SELECT 114, 11
UNION ALL SELECT 115, 11
UNION ALL SELECT 116, 11
UNION ALL SELECT 101, 10
UNION ALL SELECT 102, 10
UNION ALL SELECT 103, 10
UNION ALL SELECT 104, 10
UNION ALL SELECT 105, 10
UNION ALL SELECT 106, 10
UNION ALL SELECT 107, 10
UNION ALL SELECT 1, 0;

WITH UtHierarchy
AS (
    SELECT
        ut
        ,parenteut
        ,0 AS LEVEL
        ,ut AS root
        ,RIGHT('000000' + CAST(ut AS varchar(MAX)), 6) AS sort
    FROM #RUT
    WHERE
        parenteut = 0

    UNION ALL

    SELECT 
        RUT.ut
        ,RUT.parenteut
        ,LEVEL + 1 AS LEVEL
        ,RUT.parenteut AS root
    ,uh.sort+'/'+RIGHT('000000' + CAST(RUT.ut AS varchar(20)), 6) AS sort
    FROM #RUT AS RUT
    INNER JOIN UtHierarchy uh ON uh.ut = rut.parenteut    
    )
SELECT *
FROM UtHierarchy
ORDER BY sort

DROP TABLE #RUT;

edited Sep 3, 2014 at 22:50

answered Apr 4, 2012 at 15:00

ANisus

78.7k32 gold badges171 silver badges166 bronze badges

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

5 Comments

User.Anonymous Over a year ago

Yes, it's working ! (Except 'CASE' instead 'CAST' :) Thank you

ANisus Over a year ago

smile You are welcome. I noticed the bug too, but it is edited. Have fun coding further on!

Paul Over a year ago

But... adding this one: UNION ALL SELECT 4, 1 and it will fail... since you convert to string in the sort parameter the algorithm will only work when the strings are padded with zeros

Paul Over a year ago

I've changed CAST(RUT.ut AS varchar(20)) into right('000000' + CAST(RUT.ut AS varchar(20)), 6)

ANisus Over a year ago

@Paul You are right. Thanks for pointing it out. I've edited the answer accordingly.

Collectives™ on Stack Overflow

Recursive SQL Query with order tree

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related