sub query? - 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

General SQL Server Forums

New to SQL Server Programming

sub query?

Author

Topic

kt
Yak Posting Veteran

88 Posts

Posted - 2013-03-12 : 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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-12 : 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

52326 Posts

Posted - 2013-03-12 : 13:01:10

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kt
Yak Posting Veteran

88 Posts

Posted - 2013-03-12 : 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

52326 Posts

Posted - 2013-03-12 : 14:36:18

[code]
select [a], [b], [c]
from (select city_code, city from mytbl) d
pivot(max(city) for city_code in ([a], [b], [c])) p
[/code]

also pivot works only from sql 2005 and compatibility level 90 onwards

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kt
Yak Posting Veteran

88 Posts

Posted - 2013-03-13 : 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

52326 Posts

Posted - 2013-03-13 : 10:11:35

can you post your actually used query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-13 : 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
Yak Posting Veteran

88 Posts

Posted - 2013-03-13 : 10:34:28

woohoo !!
it worked, thank you !!!!!!!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-13 : 10:39:54

no prob...

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-13 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources