Select 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

SQL Server 2005 Forums

Transact-SQL (2005)

Select query

Author

Topic

un_maskthefuture
Starting Member

1 Post

Posted - 2008-10-02 : 14:15:12

I have a Phone Contacts table that has the contact ID, type of phone number (ex: Work, Home, Mobile) and the phone number. For example the rows look like this->
CONTACT ID_____TYPE____________NUMBER
234____________Work____________8765432196
234____________Mobile__________7174258876
43_____________Work____________3456657789
43_____________Home____________3234518890

Is there a way to select only one phone number for each Contact with preference of Work over Home and Mobile?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 14:19:46

[code]SELECT CONTACT_ID,
COALESCE(MAX(CASE WHEN TYPE='Work' THEN NUMBER ELSE NULL END),MAX(CASE WHEN TYPE='Home' THEN NUMBER ELSE NULL END),MAX(CASE WHEN TYPE='Mobile' THEN NUMBER ELSE NULL END)) AS contact_number
FROM YourTable
GROUP BY CONTACT_ID
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-02 : 15:14:44

SELECT ContactID, Number
FROM (
SELECT ContactID, Number, ROW_NUMBER() OVER (PARTITION BY ContactID ORDER BY CASE Type WHEN 'Work' THEN 1 WHEN 'Home' THEN 2 ELSE 3 END) AS RecID
FROM PhoneContacts
) AS d
WHERE RecID = 1

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources