Creating a Group By without functions -- possible?

[Alex]

Hi all,

I'm trying to create a layout of our website for Marketing to review,
and though I know how I want it presented, I'm not sure how to write
the SQL code to make it work. Here's a sample of the View I've written
with all our content:

Level Title ID ParentID
1 Clinics 1
1 Services 2
2 Surgery 1 2
2 ER 2 2
2 Radiology 3 2
2 Clinic 1 4 1
2 Clinic 2 5 1
2 Clinic 3 6 1
3 Heart 1 1
3 Lung 2 1
3 Physicians 3 4
3 Physicians 4 5
3 Physicians 5 6


And the output would basically be this:

Clinics
- Clinic 1
-- Physicians
- Clinic 2
-- Physicians
- Clinic 3
-- Physicians

Services
- Surgery
-- Heart
-- Lung
- ER
- Radilogy

Is there anyway to do this in a SQL statement, or maybe Crystal or
Access? I even tried a Pivot table in Excel, but it wants to sum
stuff, and I'm not working with numbers (sums, counts, etc). Also
since I'm not using any aggrate functions MS SQL is complaining when i
use Group By.

Thanks for any suggestions. Also though the layout is similar, the
items listed above are pulled outta the air. Hopefully I typed up
something that makes since :)

Alex

[--CELKO--]

Re: Creating a Group By without functions -- possible?

Google Nested Sets Model for trees and buy a copy of TREES &
HIERARCHIES IN SQL.

[AK]

Re: Creating a Group By without functions -- possible?

create table tree(id int, parentID int, nodename varchar(20))
insert into tree values(1,null,' CEO')
insert into tree values(2,1,'lea der of team 1')
insert into tree values(3,1,'lea der of team 2')
insert into tree values(4,2,'Jer ry, team 1')
insert into tree values(5,2,'Cho , team 1')
insert into tree values(6,3,'Raj , team 2')
insert into tree values(7,3,'Kim , team 2')
select * from tree

id parentID nodename
----------- ----------- --------------------
1 NULL CEO
2 1 leader of team 1
3 1 leader of team 2
4 2 Jerry, team 1
5 2 Cho, team 1
6 3 Raj, team 2
7 3 Kim, team 2

(7 row(s) affected)


go
create procedure dbo.display_tre e
as
begin
declare @level int, @inserted int

set nocount on

create table #tree(path varchar(20), dashes varchar(20), tree_level
int,
id int, nodename varchar(20))

insert into #tree select convert(varchar (20), id), '', 1, id, nodename
from tree where parentID is null

set @level = 1
set @inserted = 1

while (@inserted > 0) and (@level < 10)
begin
insert into #tree
select path + '.' + convert(varchar (20), tree.id), dashes + '-',
@level+1,
tree.id, tree.nodename
from #tree, tree
where #tree.tree_leve l = @level
and #tree.id = tree.parentID

set @inserted = @@rowcount
set @level = @level + 1
end

select dashes+nodename from #tree order by path
drop table #tree
end
go
exec dbo.display_tre e

----------------------------------------
CEO
-leader of team 1
--Jerry, team 1
--Cho, team 1
-leader of team 2
--Raj, team 2
--Kim, team 2



go
drop procedure dbo.display_tre e
drop table tree