combining rows where columns are disjoint

[robchiaramonte]

Hi all,
Wondering if I can do the following:

I have a tables that have IMaddresses, EmailAddresses, and PhoneNumbers. If I select these using outer joins for some Person (say PersonID 100), I get back 3 rows:

PersonID IMadd EmailAdd Phone
100 robc null null
100 null rob@home null
100 null null 555-1212

What I'd really like is:
100 robc rob@home 555-1212

Can I do this? All three of the tables are in a one-to-one relationship with a ContactMechanism table, which is in a one-to-many relationship with a PersonContactMech table.

Any thoughts?

Thanks,
Rob

EggHeadCafe - Software Developer Portal of Choice
Pete's Real Jewish Nuclear Bomb Holiday Chili
http://www.eggheadcafe.com/tutorials/aspnet/c01f8454-dbea-4426-94da-26bad47324de/petes-real-jewish-nuclea.aspx

[Plamen Ratchev]

You can do:

SELECT PersonID, MAX(IMadd), MAX(EmailAdd), MAX(Phone)
FROM ...
GROUP BY PersonID;

--
Plamen Ratchev
http://www.SQLStudio.com

[Q]

Here is one way to do it, but I am sure others in this forum might have
different way(s):
CREATE TABLE #PersonContacts (PersonID int, IMadd varchar(50), EmailAdd
varchar(100), Phone varchar(20))

INSERT INTO #PersonContacts (PersonID, IMadd)
VALUES (100,'robc');
INSERT INTO #PersonContacts (PersonID, EmailAdd)
VALUES (100,'rob@home');
INSERT INTO #PersonContacts (PersonID, Phone)
VALUES (100,'555-1212');

SELECT PersonID, MAX(IMadd), MAX(EmailAdd), MAX(Phone)

FROM #PersonContacts
WHERE
PersonID = 100
GROUP BY PersonID

"Rob Chiaramonte" wrote:

> .
>

[robchiaramonte]

That worked! Thank you so much. Great idea...it never occurred to me to use the MAX function with a string.

Best,
Rob

Plamen Ratchev wrote:

You can do:SELECT PersonID, MAX(IMadd), MAX(EmailAdd), MAX(Phone)FROM ...

11-Nov-09

You can do:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice

Refactoring with the Parameterized Factory Pattern
http://www.eggheadcafe.com/tutorials/aspnet/345d6935-5ddd-4c03-8f3e-790afdea5895/refactoring-with-the-para.aspx