Contacts with Multiple Phones in a single row.

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

Contacts with Multiple Phones in a single row.

Author

Topic

VJG
Starting Member

17 Posts

Posted - 2012-03-08 : 10:54:59

Guys, i'm new the site, seen great info so far.

I'm having a hard time combining Student Contacts numbers into a single row.

My tables look like this:

Table1.Phone

Contact ID | Phone Type | Phone | Ext
123 W 7418529632 4512
123 W2 7412583698 4125
123 C1 7411235487 null
123 H 7414562356 null
152 W 1254789542 null
152 C2 1235872135 null
162 C1 1253684451 null

Table2.Contact

Student_id | Contact_id | FName | LName | Relation
00123000 123 Joe Jones Father
00123000 152 Mary Jones Mother
00162000 162 Danny Smith Father

Table3. Student

Student_ID | FName | LName | Current_status
00123000 Mike Jones A
00162000 Eddie Smith A

What I'm needing is to get the Student_ID, Contact_ID, Cell number, if they have one(C1 or C2),Work number, if they have one(W or W1) and Ext if Contact has a Work Phone for all active students.

Note: W and C1 take precedence or W1 and C2.

desired results:

Student_id | Contact_id | CellPhone | WorkPhone | Ext
00123000 123 7411235487 7418529632 4512
00123000 152 1235872135 1254789542 null
00162000 162 1253684451 null null

What I've tried using case statements, but I end up getting multiple rows, one for the cellphone and another for the workphone.

Many Thanks to all who give feedback.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 10:58:28

so you want only one workphone and one cell phone to be listed for each contact?

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

VJG
Starting Member

17 Posts

Posted - 2012-03-08 : 11:12:19

Yes, that is correct. Only need one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 11:37:34

[code]
SELECT c.Student_id ,c.Contact_ID,
MAX(CASE WHEN LEFT([Phone Type],1)='W' THEN Phone END) AS WorkPhone,
MAX(CASE WHEN LEFT([Phone Type],1)='C' THEN Phone END) AS CellPhone,
MAX(CASE WHEN LEFT([Phone Type],1)='W' THEN Ext END) AS ext
FROM Contact c
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY ContactID,LEFT([Phone Type],1) ORDER BY [Phone Type]) AS Rn,*
FROM Phone
)p
ON p.ContactID = c.Contact_ID
AND p.Rn=1
GROUP BY c.Student_id ,c.Contact_ID
[/code]

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

VJG
Starting Member

17 Posts

Posted - 2012-03-08 : 11:43:31

Thanks! I'll give this a try. Ill post back.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 11:59:41

welcome...

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

VJG
Starting Member

17 Posts

Posted - 2012-03-08 : 12:42:16

Visakh16 Thank you!!! it was exactly what i need!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources