Select the same field with different condition

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

Transact-SQL (2008)

Select the same field with different condition

Author

Topic

jchoudja
Starting Member

41 Posts

Posted - 2012-03-20 : 13:08:08

Hi I need to select different phone number from the same field in Table name is: PhoneNumber
Fields in the table are: Person_name, Phone_number and phone_location
I want to:
Select Phone_number As [Home Phone] where phone_location = 1 Or 2
Select Phone_number As [Cell Phone] where phone_location = 4 Or 5
Select Phone_number As [Work Phone] where phone_location = 6 Or 7
I want to do this in a single query and Result should display like

Person Name | Home phone | Cell Phone

Thank you for your help

jc

amitmca
Starting Member

13 Posts

Posted - 2012-03-20 : 13:12:54

Use the CASE statement.

SELECT Person_name,
CASE WHEN phone_location IN (1,2) THEN Phone_number ELSE '-' END AS [Home Phone],
CASE WHEN phone_location IN (4,5) THEN Phone_number ELSE '-' END AS [Cell Phone],
CASE WHEN phone_location IN (6,7) THEN Phone_number ELSE '-' END AS [Work Phone]
FROM PhoneNumber

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-03-20 : 13:24:33

Since you are looking to bring it on the same line you would need a group by in that.


SELECT Person_name,
Max(CASE WHEN phone_location IN (1,2) THEN Phone_number ELSE null END) AS [Home Phone],
Max(CASE WHEN phone_location IN (4,5) THEN Phone_number ELSE null END) AS [Cell Phone],
max(CASE WHEN phone_location IN (6,7) THEN Phone_number ELSE null END) AS [Work Phone]
FROM PhoneNumber
group by Person_Name

or you can do with a pivot, or join


Select a.Person_name,a.Phone_location as Home,cell.Phone_Location as Cell, work.Phone_location as Work from
FROM PhoneNumber a
left join
( 
  select *
  from
  phonenumber aa
  and aa.phone_location IN (4,5)
) cell
on a.Person_Name = cell.Person_Name
left join
( 
  select *
  from
  phonenumber aa
  and aa.phone_location IN (6,7)
) work
on a.Person_Name = work.Person_Name
where a.Phone_Location in (1,2)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

jchoudja
Starting Member

41 Posts

Posted - 2012-03-20 : 14:35:53

It Worked like a charm. Thank you sooooooo Much.

jc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources