CODEWITHSUNDEEP
sqloracle-database
Grant Curtis
Grant Curtis

Reputation: 13

Move duplicated values into unique columns

I found a similar problem here: Move duplicate values to another column, but I'm not sure that is going to work for me.

Here's how the data is set up:

Account_ID  Phone Number  Phone Number ID
1           1             1
1           2             2
1           3             3
1           4             4
1           5             5
1           6             6
2           1             1
2           2             2
2           3             3
2           4             4
2           5             5
2           6             6

Each Account ID has one entry for each of the 6 phone numbers. I want it to look like this:

Account_ID  Phone Number 1  Phone Number 2  Phone Number 3  etc.
1           1               2               3
2           2               2               2

I've tried using a CASE statement like this:

SELECT
Account_ID,
CASE Phone Number ID
WHEN 1 THEN Phone Number END AS "Phone Number 1"
CASE Phone Number ID
WHEN 2 THEN Phone Number END AS "Phone Number 1"
etc.…
GROUP BY 
Case CASE Phone Number ID
WHEN 1 THEN Phone Number END
etc.…

But it still doesn't properly consolidate the data to a single row for each Account_ID. It put the Phone Number that corresponds with the Phone Number ID in the correct column, but each Account_ID is still it's own row.

Any thoughts? The link I supplied above is way too nested, slow, and clunky for this many fields. I wrote a version of it anyway to test it out, but it's been running for going on 15 minutes.

Thanks in advance!

Upvotes: 1

Views: 198

Answers (3)

ljh
ljh

Reputation: 2594

You can use PIVOT to get the output you need. 


select * from 
(table1 
   pivot (max("Phone Number") for "Phone Number ID"
  in ('1' as "Phone Number 1",
      '2' as "Phone Number 2",
      '3' as "Phone Number 3",
      '4' as "Phone Number 4",
      '5' as "Phone Number 5",
      '6' as "Phone Number 6"))
 )

SQL FIDDLE DEMO

Upvotes: 2

Conrad Frix
Conrad Frix

Reputation: 52655

You could do it with a bunch of joins

WITH accounts 
     AS (SELECT DISTINCT account_id 
         FROM   phones) 
SELECT a.account_id, 
       one.phone_number   phone_number_1, 
       two.phone_number   phone_number_2, 
       three.phone_number phone_number_3, 
       four.phone_number  phone_number_4, 
       five.phone_number  phone_number_5, 
       six.phone_number   phone_number_6 
FROM   accounts a 
       LEFT JOIN phones one 
              ON a.account_id = one.account_id 
                 AND one.phone_number_id = 1 
       LEFT JOIN phones two 
              ON two.account_id = two.account_id 
                 AND two.phone_number_id = 2 
       LEFT JOIN phones three 
              ON a.account_id = three.account_id 
                 AND three.phone_number_id = 3 
       LEFT JOIN phones four 
              ON a.account_id = four.account_id 
                 AND four.phone_number_id = 4 
       LEFT JOIN phones five 
              ON a.account_id = five.account_id 
                 AND five.phone_number_id = 5 
       LEFT JOIN phones six 
              ON a.account_id = six.account_id 
                 AND six.phone_number_id = 6

DEMO

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269963

You are close. You need a case and a max and a group by:

select Account_ID,
       max(CASE Phone Number ID = 1 then Phone Number end) as "Phone Number 1",
       max(CASE Phone Number ID = 2 then Phone Number end) as "Phone Number 2",
       max(CASE Phone Number ID = 3 then Phone Number end) as "Phone Number 3",
       . . .
from . . .
group by Account_Id

Upvotes: 0

Related Questions