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


Be One with the Optimizer
TG
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-26 : 14:18:44
see this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx
Go to Top of Page

sachingovekar
Posting Yak Master

101 Posts

Posted - 2009-06-28 : 03:49:15
Thanks!! all
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-28 : 13:00:54
welcome
Go to Top of Page
   

- Advertisement -