What I want to know is "is it possible to perform hierarchical select statements in SQL Server as in Oracle".
In the Oracle database tree navigation is possible. Oracle allows one attribute in the table to point to other rows in the same table.
Take the example below an organisation chart:
Create table corporate_employees (
employee_id integer primary key,
manager_id references corporate_employees,
Name varchar(100));
The integers in the manager_id are actually pointers to other rows in the table corporate_employees. This table creation in SQL Server is also possible
If we then insert data into the table and query the data, the results will display the information as an organisation chart with the employees assigned to the relevant department or manager.
insert into corporate_employees values (1,NULL,'Boss');
insert into corporate_employees values (2,1,'Marketing');
insert into corporate_employees values (3,1,'Sales');
insert into corporate_employees values (4,3,'John Sales Person');
insert into corporate_employees values (5,4,'Bill Sales Assistant');
insert into corporate_employees values (6,1,'Engineering');
insert into corporate_employees values (7,6,'Jane Doe');
insert into corporate_employees values (8,6,'Joe Bloggs');
select lpad(' ',(level-1)*2)|| name as padded_name,
employee_id,
manager_id,
level
from corporate_employees
connect by prior employee_id = manager_id
start with employee_id =1;
Results from “select” query:
Padded_name employee_id manager_id level
Boss 1 1
Marketing 2 1 2
Sales 3 1 2
John Sales Person 4 3 3
Bill Sales Assist 5 4 4
Engineering 6 1 2
Jane Doe 7 6 3
Joe Bloggs 8 6 3
The select statement is possible in Oracle as Oracle allows the user to get all the relevant rows using "Connect by" and the "level" column is the Oracle's pseudo-column which allows the data to be shown hierarchically.
Unfortunately 'connect by' and level are unrecognised in SQL Server.
The question is "is there any equivalent for 'Connect by' and 'level' used in SQL SERVER sql statements to display the results as above? Can you help?
Thanks.
In the Oracle database tree navigation is possible. Oracle allows one attribute in the table to point to other rows in the same table.
Take the example below an organisation chart:
Create table corporate_employees (
employee_id integer primary key,
manager_id references corporate_employees,
Name varchar(100));
The integers in the manager_id are actually pointers to other rows in the table corporate_employees. This table creation in SQL Server is also possible
If we then insert data into the table and query the data, the results will display the information as an organisation chart with the employees assigned to the relevant department or manager.
insert into corporate_employees values (1,NULL,'Boss');
insert into corporate_employees values (2,1,'Marketing');
insert into corporate_employees values (3,1,'Sales');
insert into corporate_employees values (4,3,'John Sales Person');
insert into corporate_employees values (5,4,'Bill Sales Assistant');
insert into corporate_employees values (6,1,'Engineering');
insert into corporate_employees values (7,6,'Jane Doe');
insert into corporate_employees values (8,6,'Joe Bloggs');
select lpad(' ',(level-1)*2)|| name as padded_name,
employee_id,
manager_id,
level
from corporate_employees
connect by prior employee_id = manager_id
start with employee_id =1;
Results from “select” query:
Padded_name employee_id manager_id level
Boss 1 1
Marketing 2 1 2
Sales 3 1 2
John Sales Person 4 3 3
Bill Sales Assist 5 4 4
Engineering 6 1 2
Jane Doe 7 6 3
Joe Bloggs 8 6 3
The select statement is possible in Oracle as Oracle allows the user to get all the relevant rows using "Connect by" and the "level" column is the Oracle's pseudo-column which allows the data to be shown hierarchically.
Unfortunately 'connect by' and level are unrecognised in SQL Server.
The question is "is there any equivalent for 'Connect by' and 'level' used in SQL SERVER sql statements to display the results as above? Can you help?
Thanks.
