SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sub query?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kt
Yak Posting Veteran

85 Posts

Posted - 03/12/2013 :  10:27:48  Show Profile  Reply with Quote
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
6062 Posts

Posted - 03/12/2013 :  12:04:10  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/12/2013 :  13:01:10  Show Profile  Reply with Quote
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

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

Go to Top of Page

kt
Yak Posting Veteran

85 Posts

Posted - 03/12/2013 :  14:34:02  Show Profile  Reply with Quote
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'."
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/12/2013 :  14:36:18  Show Profile  Reply with Quote

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/

Go to Top of Page

kt
Yak Posting Veteran

85 Posts

Posted - 03/13/2013 :  08:53:58  Show Profile  Reply with Quote
my verion is 2012.
still didn't work, got the same error message. Any other sugesstions?

thank
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/13/2013 :  10:11:35  Show Profile  Reply with Quote
can you post your actually used query?

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

Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 03/13/2013 :  10:20:41  Show Profile  Reply with Quote
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
Go to Top of Page

kt
Yak Posting Veteran

85 Posts

Posted - 03/13/2013 :  10:34:28  Show Profile  Reply with Quote
woohoo !!
it worked, thank you !!!!!!!
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 03/13/2013 :  10:39:54  Show Profile  Reply with Quote
no prob...

Be One with the Optimizer
TG
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/13/2013 :  10:59:33  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.36 seconds. Powered By: Snitz Forums 2000