Crosstab Select!! - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Crosstab Select!!

Author

Topic

nice123ej
Starting Member

48 Posts

Posted - 2008-04-03 : 00:48:07

Hi
I have table called MyTable that has 3 columns (City , Brand, Price)
I want to select from this table in Cross Tab format
i.e.
My table has
City Brand Price
London DELL 1227
London Toshiba 1100
London Acer 1007
London HP 1467
London IBM 1193
Sydney Toshiba 2100
Sydney Acer 2219
Sydney Apple 2589
Sydney VAIO 2122
Sydney HP 1929
Sydney IBM 2877
Tokyo Toshiba 7200
Tokyo Acer 5299
Tokyo COMPAQ 9200
Tokyo IBM 8779
Tokyo HP 6286
Paris DELL 1670
Paris Apple 1825
Paris VAIO 1267
Paris HP 1882
Paris COMPAQ 1636
Paris IBM 1332
NewYork DELL 2000
NewYork Toshiba 1288
NewYork Acer 2333
NewYork Apple 2299
NewYork VAIO 2327

__________________________________

i want the select statment result to be like this
DELL Toshiba Acer HP IBM Apple VAIO COMPAQ
London 1227 1100 1007 1467 1193 N/A N/A N/A
Sydney N/A 2100 2219 1929 2877 N/A N/A N/A
Tokyo N/A 7200 5299 6286 8779 N/A N/A 9200
Paris 1670 N/A N/A 1882 1332 1825 1267 1636
NewYork 2000 1288 2333 N/A N/A 2299 2327 N/A

City & Brand can be anything so i will not be able to hard code them in my Select.

who can help with that?!

ranganath
Posting Yak Master

209 Posts

Posted - 2008-04-03 : 01:45:23

Hi,
try with this

Create Table #Temp (City Varchar(1000), Brand varchar(100),Price Int)
Insert into #Temp
Select 'London' , 'DELL', 1227 Union All
Select 'London' , 'Toshiba', 1100 Union All
Select 'London' , 'Acer', 1007 Union All
Select 'London' , 'HP', 1467 Union All
Select 'London' , 'IBM' ,1193 Union All
Select 'Sydney' , 'Toshiba', 2100 Union All
Select 'Sydney' , 'Acer', 2219 Union All
Select 'Sydney' , 'Apple' ,2589 Union All
Select 'Sydney' , 'VAIO' ,2122 Union All
Select 'Sydney' , 'HP' ,1929 Union All
Select 'Sydney' , 'IBM' ,2877 Union All
Select 'Tokyo ' ,'Toshiba', 7200 Union All
Select 'Tokyo ' ,'Acer' ,5299 Union All
Select 'Tokyo ' ,'COMPAQ' ,9200 Union All
Select 'Tokyo ' ,'IBM', 8779 Union All
Select 'Tokyo ' ,'HP' ,6286 Union All
Select 'Paris ' , 'DELL', 1670 Union All
Select 'Paris ' , 'Apple', 1825 Union All
Select 'Paris ' , 'VAIO', 1267 Union All
Select 'Paris ' , 'HP', 1882 Union All
Select 'Paris ' , 'COMPAQ', 1636 Union All
Select 'Paris ' , 'IBM' ,1332 Union All
Select 'NewYork' , 'DELL' ,2000 Union All
Select 'NewYork' , 'Toshiba', 1288 Union All
Select 'NewYork' , 'Acer' ,2333 Union All
Select 'NewYork' , 'Apple', 2299 Union All
Select 'NewYork' , 'VAIO' ,2327

Declare @Sql Varchar(Max), @Str Varchar(Max)
Set @Sql = ''
Set @str = ''
Select @sql = @Sql + ', Min(Case when Brand = ''' + Brand +''' Then Price End ) As "'+ Brand+'"'
From (Select Distinct Brand From #Temp)A

Select @str = @str + 'Select City'+(@sql)+' From #Temp Group By City'
print @str
Exec (@str)

Drop Table #Temp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-03 : 10:29:36

or use PIVOT clause with dynamic sql.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources