hierarchy structure

hello!
I have a hierarchy structure copied from excel to a table in databse SQL.
Here is what i have in SQL table:
ContinentCountryCity
Europe
Norway
Oslo
Bergen
Trondheim
Sweden
Stockholm
Gotaborg
Denmark
Kopenhagen
Aalborg
Germany
Hamburg
Munchen
Hannover
Asia
Japan
Tokyo
Hirroshima
Kina
Shanghai
Africa
South-Africa
Pretoria
AND her is what i want
ContinentCountryCity
Europe
EuropeNorway
EuropeNorwayOslo
EuropeNorwayBergen
EuropeNorwayTrondheim
EuropeSweden
EuropeSwedenStockholm
EuropeSwedenGotaborg
EuropeDenmark
EuropeDenmarkKopenhagen
EuropeDenmarkAalborg
EuropeGermany
EuropeGermanyHamburg
EuropeGermanyMunchen
EuropeGermanyHannover
Asia
AsiaJapan
AsiaJapanTokyo
AsiaJapanHirroshima
AsiaKina
AsiaKinaShanghai
AfricaKina
AfricaSouth-Africa
AfricaSouth-AfricaPretoria
How do i that?
On Wed, 25 May 2005 02:23:02 -0700, CJ wrote:

>hello!
>I have a hierarchy structure copied from excel to a table in databse SQL.
>Here is what i have in SQL table:
(snip)
Hi CJ,
Do you mean that each row has either Continent, or Country, or City
populated, but none of the other columns? I guess you're out of luck
then. Since there is no inherent order in a relational table, there is
no difference between the table you posted and this one:
ContinentCountryCity
Africa
Asia
Europe
Denmark
Germany
Japan
Kina
Norway
South-Africa
Sweden
Aalborg
Bergen
Gotaborg
Hannover
Hamburg
Hirroshima
Kopenhagen
Munchen
Oslo
Pretoria
Stockholm
Shanghai
Tokyo
Trondheim
Besides - since this table has no key, it is officially not a relational
table at all. Of course, terminology is pretty much a question of
definitions, but a table without primary key is usually impossible to do
any serious operations on.

>AND her is what i want
>ContinentCountryCity
>Europe
>EuropeNorway
>EuropeNorwayOslo
>EuropeNorwayBergen
>EuropeNorwayTrondheim
>EuropeSweden
>EuropeSwedenStockholm
>EuropeSwedenGotaborg
>EuropeDenmark
>EuropeDenmarkKopenhagen
>EuropeDenmarkAalborg
>EuropeGermany
>EuropeGermanyHamburg
>EuropeGermanyMunchen
>EuropeGermanyHannover
>Asia
>AsiaJapan
>AsiaJapanTokyo
>AsiaJapanHirroshima
>AsiaKina
>AsiaKinaShanghai
>AfricaKina
>AfricaSouth-Africa
>AfricaSouth-AfricaPretoria
>How do i that?
You don't. The data above has no key either, so it's not a relational
table. Consider moving the data to some properly normalized tables:
CREATE TABLE Countries
(Country varchar(20) NOT NULL,
Continent varchar(10) NOT NULL,
PRIMARY KEY (Country)
)
CREATE TABLE Cities
(City varchar(25) NOT NULL,
Country varchar(20) NOT NULL,
PRIMARY KEY (City),
FOREIGN KEY (Country) REFERENCES Countries (Country)
)
INSERT INTO Countries (Country, Continent)
VALUES ('Norway', 'Europe')
INSERT INTO Countries (Country, Continent)
VALUES ('Sweden', 'Europe')
....
INSERT INTO Countries (Country, Continent)
VALUES ('South-Africa', 'Africa')
INSERT INTO Cities (City, Country)
VALUES ('Oslo', 'Norway')
INSERT INTO Cities (City, Country)
VALUES ('Bergen', 'Norway')
....
INSERT INTO Cities (City, Country)
VALUES ('Pretoria', 'South-Africa')
The input needed to get the listing you describe above would be returned
by
SELECT co.Continent, co.Country, ci.City
FROM Countries AS co
INNER JOIN Cities AS ci
ON ci.Country = co.Country
ORDER BY co.Continent, co.Country, ci.City
The final reformatting to get it displayed exectly as above (including
the repeating of some lines with partial blank data) should be handled
by the presentation tier.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)