October 6, 2009 at 9:12 am
I have a table which has two columns and multiple rows. The first column lists the countries and the second coulumn lists the cities for that particular country. See below:
Algeria Algiers
Algeria Oran
Argentina Buenos Aires
Argentina Catamarca
Andorra Canillo
ArmeniaYerevan
Australia Sydney
Australia Melbourne
Australia Brisbane
Australia Perth
Bosnia Banja Luka
Bulgaria Sofia
BrazilSao Paulo
BrazilSalvador
BrazilBrasilia
BrazilCuritiba
BrazilBelem
I want to write a query which converts the results so that the countries are listed as the coulumn headings and the cities listed below the country. i.e:
Algeria Argentina Andorra
Algiers Buenos Aires Canillo
Oran Catamarca
and so on....
So for the example above, I would have eight country coulmns, one for each country, and multiple rows for the cities.
Is this possible?
October 6, 2009 at 2:04 pm
Global answer: yes.
The major question would be: Why would you do this?
A relational database is designed following a simple pattern:
- data in a row belong together
- data in one column have the same "meaning"
Your sample data will meet those requirements (col 1: country, col2: city). Your target data structure won't (each row will contain data with no relation to each other).
It looks like you're treating a database like an Excel spreadsheet, where you simply can transpose cell values....
Would the following "result set" be valid?
Algeria Argentina Andorra
Algiers Catamarca Canillo
Oran Buenos Aires
See what I mean?
If you insist on getting that query you might want to have a look at Jeff Modens article regarding Dynamic Cross Tabs[/url].
You would need to find a way to identify the city sequence per country though. With SS2K5 I'd recommend ROW_NUMBER but since we're on SS2K you might want to look for a substitute.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply