CODEWITHSUNDEEP
mysqldatabase
William Willden
William Willden

Reputation: 123

SQL statement for unique rows

I am attempting to extract information from a table that holds account numbers, phone numbers, and emails. However, I'm not sure what whoever designed this table was thinking. The table is set up like this.

AccountID | PhoneType | PhoneNumber

Now you might be asking where are the emails stored. They are stored under phone number. An example of this database.

AccountID | PhoneType | PhoneNumber
123456        WORK        111-1111
123456        MOBILE      111-4567
123456        EMAIL     [email protected]
222222        EMAIL      [email protected]

I'm not sure how to do this as I have just a rudimentary knowledge of SQL. We would ideally want things like:

AccountID | WorkPhone | MobilePhone | Email
123456       111-1111    111-4567     [email protected]
222222                                [email protected]

What is a good way to go about doing this? I've tried a couple IF statements but nothing that gets me to what I would want.

Upvotes: 1

Views: 66

Answers (3)

Richard Zhang
Richard Zhang

Reputation: 350

If phone types are limited known set, then simply Join. Something like this:

SELECT DISTINCT a.AccountId,
       wp.PhoneNumber as WorkPhone,
       mp.PhoneNumber as MobilePhone,
       em.PhoneNumber as Email
    FROM MyTable As a
    LEFT JOIN (SELECT AccountId, PhoneNumber FROM MyTable
               WHERE PhoneType = 'WORK') As wp
         ON a.AccountId = wp.AccountId
    LEFT JOIN (SELECT AccountId, PhoneNumber FROM MyTable
               WHERE PhoneType = 'MOBILE') As mp
         ON a.AccountId = mp.AccountId
    LEFT JOIN (SELECT AccountId, PhoneNumber FROM MyTable
               WHERE PhoneType = 'EMAIL') As em
         ON a.AccountId = em.AccountId

Upvotes: -1

CodeLikeBeaker
CodeLikeBeaker

Reputation: 21312

Something like this would work. If you had more phone types, this could get long:

Edit: Based on xQbert's response, he was correct. You need to add a group by on Account ID, otherwise you will still get multiple rows. 

SELECT
    AccountId
    , MAX(CASE WHEN PhoneType = 'WorkPhone' THEN
        PhoneNumber
      ELSE
        NULL
    END) AS 'WorkPhone'
    , MAX(CASE WHEN PhoneType = 'MobilePhone' THEN
        PhoneNumber
      ELSE
        NULL
    END) AS 'MobilePhone'
    , MAX(CASE WHEN PhoneType = 'Email' THEN
        PhoneNumber
      ELSE
        NULL
    END) AS 'Email'
FROM
    MyTable
GROUP BY AccountId

Upvotes: 3

xQbert
xQbert

Reputation: 35343

Just building on CodeLikeBeaker's response.

Distinct won't do it as each record is unique when pivoted using the case. However by using a max() aggregate on each case and a group by we can combine the records.

SELECT AccountId
     , max(CASE WHEN PhoneType = 'WorkPhone' THEN PhoneNumber END) AS 'WorkPhone'
     , max(CASE WHEN PhoneType = 'MobilePhone' THEN PhoneNumber END) AS 'MobilePhone'
     , max(CASE WHEN PhoneType = 'Email' THEN PhoneNumber END) AS 'Email'
FROM MyTable
GROUP BY accountID

Upvotes: 1

Related Questions