Recursive SQL query with order tree
I have a problem ordering the request as a tree like 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
It works like at level 0 or 1, but for higher level it is broken. I'm trying to select in the root column the parent level "0" for the order from root and ut, but after a while on this problem I can't :(
How do I resolve this?
Thank you for your help.
EDIT : Thanks for editing with sql colors :) I saw a solution for the topmost level, but the user 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 doesn't work either
Here is 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, this is not a good structure. I need a tree like 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
source to share
ince Recursive call is correct, your problem is sorting the result
ORDER BY root
You can try to create a sort path to help get them in the correct 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 error)
Edit 2 (adding a working example from test data):
Here you have some test code for copy and paste. Works great 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;
source to share