SQL Server Equivalent of Oracle 'CONNECT BY PRIOR', and 'ORDER SIBLINGS BY'
Simulating the LEVEL column
The level column can easily be simulated by incrementing a counter in the recursive part:
WITH tree (empid, name, level) AS (
SELECT empid, name, 1 as level
FROM emp
WHERE name = 'Joan'
UNION ALL
SELECT child.empid, child.name, parent.level + 1
FROM emp as child
JOIN tree parent on parent.empid = child.mgrid
)
SELECT name
FROM tree;
Simulating order siblings by
Simulating the order siblings by
is a bit more complicated. Assuming we have a column sort_order
that defines the order of elements per parent (not the overall sort order - because then order siblings
wouldn't be necessary) then we can create a column which gives us an overall sort order:
WITH tree (empid, name, level, sort_path) AS (
SELECT empid, name, 1 as level,
cast('/' + right('000000' + CONVERT(varchar, sort_order), 6) as varchar(max))
FROM emp
WHERE name = 'Joan'
UNION ALL
SELECT child.empid, child.name, parent.level + 1,
parent.sort_path + '/' + right('000000' + CONVERT(varchar, child.sort_order), 6)
FROM emp as child
JOIN tree parent on parent.empid = child.mgrid
)
SELECT *
FROM tree
order by sort_path;
The expression for the sort_path
looks so complicated because SQL Server (at least the version you are using) does not have a simple function to format a number with leading zeros. In Postgres I would use an integer array so that the conversion to varchar
isn't necessary - but that doesn't work in SQL Server either.
user1058946
An ex-C# programmer and current Technical Business Analyst by profession, with a keen interest in marketing and advertising. Weird ... I know!
Updated on June 10, 2020Comments
-
user1058946 almost 4 years
I've got this Oracle code structure I'm trying to convert to SQL Server 2008 (Note: I have used generic names, enclosed column names and table names within square brackets '[]', and done some formatting to make the code more readable):
SELECT [col#1], [col#2], [col#3], ..., [col#n], [LEVEL] FROM (SELECT [col#1], [col#2], [col#3], ..., [col#n] FROM [TABLE_1] WHERE ... ) CONNECT BY PRIOR [col#1] = [col#2] START WITH [col#2] IS NULL ORDER SIBLINGS BY [col#3]
What is the SQL Server equivalent template of the above code?
Specifically, I'm struggling with the LEVEL, and 'ORDER SIBLINGS BY' Oracle constructs.
Note: The above "code" is the final output from a set of Oracle procedures. Basically, the 'WHERE' clause is built up dynamically and changes depending on various parameters passed. The code block starting with 'CONNECT BY PRIOR' is hard-coded.
For Reference:
The Simulation of CONNECT BY PRIOR of ORACLE in SQL SERVER article comes close, but it does not explain how to handle the 'LEVEL' and the 'ORDER SIBLINGS' constructs. ... And my mind is getting in a twist!
SELECT name FROM emp START WITH name = 'Joan' CONNECT BY PRIOR empid = mgrid
equates to:
WITH n(empid, name) AS (SELECT empid, name FROM emp WHERE name = 'Joan' UNION ALL SELECT nplus1.empid, nplus1.name FROM emp as nplus1, n WHERE n.empid = nplus1.mgrid) SELECT name FROM n
If I have an initial template to work from, it will go a long way to helping me construct SQL Server stored procs to build up a correct T-SQL statement.
Assistance will be much appreciated.
-
user1058946 almost 9 yearsOK. Thanks, I like that. That's 'LEVEL' sorted. :) Any idea how to handle ORDER SIBLINGS?
-
user1058946 almost 9 yearsYes! This looks like it might work. Assuming that sort_order column is already just a text string something like 'R301 - Area Management' or 'R402 - Casual Staff' - then it should just work ... I hope. The original Oracle code seems to only sort by the 'child'. It displays the 'parent_code' separately for every line in the results. ... I'm trying this now. :)