| Author |
 Topic  |
|
|
kt
Starting Member
38 Posts |
 Posted - 03/12/2013 : 10:27:48
|
hi,
i have the table as below
id city_code city
1 A Austin
2 D Dallas
3 H Houston
1: result is Austin
select city
from tbl
where city_code ='A'
2: result is Dallas
select city
from tbl
where city_code ='D'
3. Result is Houston
select city
from tbl
where city_code ='H'
instead of three queries, is there the way to combine then all into one query to have results city for ech city_code?
A D H
Austin Dallas Houston
Can anyone pls help?
thanks |
Edited by - kt on 03/12/2013 11:38:18
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 03/12/2013 : 12:04:10
|
Here's one way:
select [A], [D], [H]
from (select city_code, city from <yourTable>) d
pivot (max(city) for city_code in ([A], [D], [H])) p
If you won't know all the possible city_codes at design time then search for "dynamic pivot" or "dynamic crosstab"
Be One with the Optimizer
TG |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
|
|
kt
Starting Member
38 Posts |
Posted - 03/12/2013 : 14:34:02
|
thanks
i try this:
select [a], [b], [b]
from (select city_code, city from mytbl) d
pivot(max(city_code) for city_code in ([a], [a], [c])) p
but got an error"Incorrect syntax near 'pivot'." |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 03/12/2013 : 14:36:18
|
select [a], [b], [c]
from (select city_code, city from mytbl) d
pivot(max(city) for city_code in ([a], [b], [c])) p
also pivot works only from sql 2005 and compatibility level 90 onwards
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
kt
Starting Member
38 Posts |
Posted - 03/13/2013 : 08:53:58
|
my verion is 2012.
still didn't work, got the same error message. Any other sugesstions?
thank |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 03/13/2013 : 10:11:35
|
can you post your actually used query?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 03/13/2013 : 10:20:41
|
Then "incorrect syntax near 'pivot'" error would indicate your server is not 2005 or later or your db compatibility level is below 90. This should work for any version:
select [A] = max(case when city_code = 'A' then city end)
,[D] = max(case when city_code = 'D' then city end)
,[H] = max(case when city_code = 'H' then city end)
from <yourTable>
Be One with the Optimizer
TG |
|
|
|
kt
Starting Member
38 Posts |
Posted - 03/13/2013 : 10:34:28
|
woohoo !!
it worked, thank you !!!!!!! |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 03/13/2013 : 10:39:54
|
no prob...
Be One with the Optimizer
TG |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 03/13/2013 : 10:59:33
|
quote:
Originally posted by kt
woohoo !!
it worked, thank you !!!!!!!
then it was obvious that your version was before 2005
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
sub query?
