Solved

Hierarchical Queries

Posted on 2001-06-09
2
636 Views
Last Modified: 2008-03-10
Hi

Does SQL Server 2000 supports hierarchical queries like Oracle.

Suppose I have a table

ID | PARENT ID | NAME
1  |           | A
2  |           | B
3  | 1         | C

I would like to select all records in this order:
1
3
2

With Oracle I could use connect by statement.

Saar
0
Comment
Question by:saar2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 18

Accepted Solution

by:
nigelrivett earned 50 total points
ID: 6173048
No but it is relatively easy to construct an SP to do this.

Heres one I did earlier

General way to handle trees. Fails when the squence varchar length is exceeded - in this case 100 levels.
Problems for more than 800.

create table #z (id int, name varchar(20), threadID int null)
insert #z select   1,  'foo', null
insert #z select     2,  'foo2',   null
insert #z select     3,  'foo2a',      2
insert #z select     4,  'foo2b',      2
insert #z select     5,  'foo3',   null
insert #z select     6,  'foo3b',      5
insert #z select     7,  'foo3ba',     6
insert #z select     8,  'foo4',   null
insert #z select     9,  'fooa',       1
insert #z select    10,  'foo2ba',     4

drop table #a
go
create table #a (id int, sequence varchar(1000), levelNo int)
insert #a select id, right(space(10) + convert(varchar(10),id),10), 1 from #z where threadid is null
declare @i int
select @i = 0
while @@rowcount > 0
begin
select @i = @i + 1
insert #a
select #z.id, sequence + right(space(10) + convert(varchar(10),#z.id),10), @i + 1
from #z, #a
where #a.levelNo = @i
and #z.threadID = #a.id
end

select #z.name
from #a, #z
where #a.id = #z.id
order by sequence
0
 
LVL 4

Author Comment

by:saar2
ID: 6173940
This is yes/no question.

You got the points.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question