Looking for some help with rows into columns

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

General SQL Server Forums

New to SQL Server Programming

Looking for some help with rows into columns

Author

Topic

Mike44
Starting Member

2 Posts

Posted - 2009-10-06 : 10:59:23

I want to convert the following table of countries with cities:

Algeria - Algiers
Algeria - Oran
Argentina - Buenos Aires
Argentina - Catamarca
Andorra - Canillo
Armenia - Yerevan
Australia - Sydney
Australia- Melbourne
Australia- Brisbane
Australia- Perth
Bosnia - Banja Luka
Bulgaria - Sofia
Brazil - Sao Paulo
Brazil - Salvador
Brazil - Brasilia
Brazil - Curitiba
Brazil - Belem

so that the result looks like (but for all countries):

Algeria Argentina Australia
Algiers Buenos Aires Sydney
Oran Catamarca Melbourne

Sorry the formatting is not very good!

Basically the countries will be listed as a column heading and all the cities listed underneath as a simple table.

Is this possible?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-06 : 11:36:36

I guess the question is: why? With almost 200 possible countries that could be a lot of columns. Next question is there a set list of countries you want columns for or is it for only whatever countries that are in your table at the time the query runs? Final question is are you using sql server 2005 or later?

Be One with the Optimizer
TG

Mike44
Starting Member

2 Posts

Posted - 2009-10-06 : 11:46:43

Hi,

Thanks for the reply. I understand that thier could be a lot of columns but this is ok. I need the data formatted in that way because I will be using other applications i.e. Excel to plug into the sql table and certain functions that i will be using in excel only accepts data in that format.

The number of countries is not set. i.e. the number of countries could increase or even the number of cities for each country could grow or decrease. This will depend on which country/city we need at the time. I am using SQL 2005.

Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-06 : 12:44:16

Since you want it in excel have you considered linking/importing excel to the table as it is and using excel's pivotTable functionality for this? sounds like an ideal fit.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-06 : 13:35:02

Here is away to do it in Sql 2005:

I removed a couple countries from the output so it would fit here.


set nocount on
create table #yourTable (country varchar(20), city varchar(20))
go
insert #yourTable
select 'Algeria', 'Algiers'
union all select 'Algeria', 'Oran'
union all select 'Argentina', 'Buenos Aires'
union all select 'Argentina', 'Catamarca'
union all select 'Andorra', 'Canillo'
union all select 'Armenia', 'Yerevan'
union all select 'Australia', 'Sydney'
union all select 'Australia', 'Melbourne'
union all select 'Australia', 'Brisbane'
union all select 'Australia', 'Perth'
union all select 'Bosnia', 'Banja Luka'
union all select 'Bulgaria', 'Sofia'
union all select 'Brazil', 'Sao Paulo'
union all select 'Brazil', 'Salvador'
union all select 'Brazil', 'Brasilia'
union all select 'Brazil', 'Curitiba'
union all select 'Brazil', 'Belem'
go

declare @colList varchar(8000)
select @colList = coalesce(@colList + ', [' + country + ']', '[' + country + ']') from #yourTable group by country

exec('
select ' + @colList + '
from   (select country, city, row_number() over (partition by country order by city) as rn from #yourTable) t
pivot (max(city) for country in (' + @colList + ')) as p')

go
drop table #yourTable

OUTPUT:

Algeria             Argentina            Australia            Bosnia               Brazil               Bulgaria
---------------------------------------- -------------------- -------------------- -------------------- --------------------
Algiers             Buenos Aires         Brisbane             Banja Luka           Belem                Sofia
Oran                Catamarca            Melbourne            NULL                 Brasilia             NULL
NULL                NULL                 Perth                NULL                 Curitiba             NULL
NULL                NULL                 Sydney               NULL                 Salvador             NULL
NULL                NULL                 NULL                 NULL                 Sao Paulo            NULL

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-07 : 02:33:19

As you want to show data in EXCEL, pivot data there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources