display data horizontally without hardcoding

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

SQL Server Development (2000)

display data horizontally without hardcoding

Author

Topic

sachingovekar
Posting Yak Master

101 Posts

Posted - 2009-06-26 : 11:28:30

Hi,

I have count of users for an application for a particular country.

using my query the data is displayed vertically.

I want to display the data horizontally without harcoding the country as there are many countries.

create table #temp1
(
application varchar(100),
country varchar(100),
users int
)

insert into #temp1 values ('abc','belgium','3')
insert into #temp1 values ('abc','canada','3')
insert into #temp1 values ('abc','uae','3')
insert into #temp1 values ('abc','france','3')
insert into #temp1 values ('xyz','ukarine','3')
insert into #temp1 values ('xyz','denmark','3')
insert into #temp1 values ('xyz','solvakia','3')
insert into #temp1 values ('xyz','japan','3')
insert into #temp1 values ('xyz','china','3')
insert into #temp1 values ('efg','usa','3')
insert into #temp1 values ('efg','hongkong','3')
insert into #temp1 values ('efg','malaysia','3')
insert into #temp1 values ('lmn','indonesia','3')
insert into #temp1 values ('efg','brazil','3')

out put required

application belgium china france etc as columns
abc and then count of users

I know CASE can be used but i don't want to harcode countries as there are n number of countries in data.

Regards,
sachin

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-26 : 11:53:28

Search for "Dynamic Cross Tab". But it would be something like this:


declare @case varchar(8000)
set @case = ''
select @case = @case 
              + '     ,sum(case when country = ''' + country + ''' then users else 0 end) as [' + country + ']' + char(13)
from   #temp1 
group by country

exec( '
select application
' + @case + '
from #temp1
group by application')

application   belgium     brazil      canada      china       denmark     france      hongkong    indonesia ETC....
------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ---------
abc           3           0           3           0           0           3           0           0        
efg           0           3           0           0           0           0           3           0        
lmn           0           0           0           0           0           0           0           3        
xyz           0           0           0           3           3           0           0           0