How do I query this - Hierarchy

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How do I query this - Hierarchy

Author

Topic

dantesfu
Starting Member

7 Posts

Posted - 2006-12-07 : 03:00:13

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 03:28:07

[code]-- prepare test data
declare @test table (Country varchar(6), Region varchar(7), City varchar(7))

insert @test
select 'USA', 'Iowa', 'Ames' union all
select 'USA', 'Utha', 'Liberty' union all
select 'Canada', 'Ontario', 'Toronto'

-- stage the data
declare @stage table (Location varchar(7), LocationCode varchar(8))

insert @stage
select distinct t1.country,
right('0' + CAST((select count(distinct t2.country) from @test t2 where t2.country <= t1.country) as varchar), 2)
from @test t1

insert @stage
select distinct t1.region,
(select s.locationcode from @stage s where t1.country = s.location and s.locationcode like '__') + '-' + right('0' + CAST((select count(distinct t2.region) from @test t2 where t2.region <= t1.region and t2.country = t1.country) as varchar), 2)
from @test t1

insert @stage
select distinct t1.city,
(select s.locationcode from @stage s where t1.region = s.location and s.locationcode like '__-__') + '-' + right('0' + CAST((select count(distinct t2.city) from @test t2 where t2.city <= t1.city and t2.country = t1.country and t2.region = t1.region) as varchar), 2)
from @test t1

update @stage
set locationcode = left(locationcode + '-00-00', 8)

select * from @stage order by locationcode[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 03:36:12

[code]-- prepare test data
declare @test table (LocationCode varchar(8), Location varchar(7))

insert @test
select '01-00-00', 'USA' union all
select '02-00-00', 'Canada' union all
select '01-04-00', 'Iowa' union all
select '01-04-01', 'Ames' union all
select '01-07-00', 'Utah' union all
select '01-07-01', 'Liberty' union all
select '02-01-00', 'Ontario' union all
select '02-01-01', 'Toronto'

select c.Country,
r.Region,
q.City
from (
select location Country,
left(locationcode, 2) countrycode
from @test
where locationcode like '[0-9][0-9]-00-00'
) c
inner join (
select location Region,
left(locationcode, 2) countrycode,
left(locationcode, 5) regioncode
from @test
where locationcode like '[0-9][0-9]-[0-9][0-9]-00'
) r on r.countrycode = c.countrycode
inner join (
select location City,
left(locationcode, 5) regioncode,
locationcode
from @test
where right(locationcode, 2) <> '00'
) q on q.regioncode = r.regioncode
order by q.locationcode[/code]
Peter Larsson
Helsingborg, Sweden

dantesfu
Starting Member

7 Posts

Posted - 2006-12-07 : 07:24:39

Thank you Peso for the advice.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources