SQL Pivot and Coalesce-CodePudding

I have a SQL server table of phone numbers in a long format that I would like to pivot wider and condense/coalesce and save as a view. There are currently 5 different phone number types and I want to condense that down to 2 per account based on a defined priority(dropping any extra). Is this something I can do completely in a SQL query? I am currently querying and doing the manipulations in R. The reproducible example data frames are created in R.

original <- data.frame (Account  = c(rep("1", 3), rep("2",2), rep("3", 2)),
                  Phone.Type = c("Home", "Primary1", "Primary2", "Cell", "Primary1", "Home", "Work"),
                  Phone.Number = c("111-222-333", "222-333-4444", "333-444-5555", "444-555-6666", "555-666-7777", "666-777-8888", "777-888-9999")
                  )

priority <- c("Primary1", "Primary2", "Cell", "Home", "Work")

desiredFormat <- data.frame(Account = c("1", "2", "3"),
                           Phone.Number.1 = c("222-333-4444", "555-666-7777", "666-777-8888"),
                           Phone.Number.2 = c("333-444-5555", "444-555-6666", "777-888-9999"))

CodePudding user response：

You could do something like:

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE IF NOT EXISTS `example` (
  `account` int(1) NOT NULL,
  `type` varchar(200) NOT NULL,
  `number` varchar(200) NOT NULL
);
INSERT INTO `example` (`account`, `type`, `number`) VALUES
  (1, 'Home', '111-222-333'),
  (1, 'Primary1', '222-333-4444'),
  (1, 'Primary2', '333-444-5555'),
  (2, 'Cell', '444-555-6666'),
  (2, 'Primary1', '555-666-7777'),
  (3, 'Home', '666-777-8888'),
  (3, 'Work', '777-888-9999');

Query 1:

SELECT account,
       PhoneNumber1,
       CASE WHEN PhoneNumber2 = PhoneNumber1 THEN Other ELSE PhoneNumber2 END AS PhoneNumber2
       FROM (
SELECT account,
       COALESCE(MAX(CASE WHEN type = 'Primary1' THEN number END), number) AS PhoneNumber1,
       COALESCE(MAX(CASE WHEN type = 'Primary2' THEN number END), number) AS PhoneNumber2,
       MAX(CASE WHEN type REGEXP 'Home|Cell|Work' THEN number END) AS Other
FROM example
GROUP BY account
  ) Q1

Results:

| account | PhoneNumber1 | PhoneNumber2 |
|---------|--------------|--------------|
|       1 | 222-333-4444 | 333-444-5555 |
|       2 | 555-666-7777 | 444-555-6666 |
|       3 | 666-777-8888 | 777-888-9999 |

CodePudding user response：

Using SQLite but avoiding constructs which are specific to it, we create a priority field and then a rank field, r, giving tmp2 and from that extract the rank 1 rows into p1 and rank 2 rows into p2. Then left join p1 to p2 on Account placing the rank 1 and rank 2 phone numbers in separate columns.

library(sqldf)

sqldf("with tmp as (select *, 
    1 * ([Phone.Type] = 'Primary1')  
    2 * ([Phone.Type] = 'Primary2')  
    3 * ([Phone.Type] = 'Cell')  
    4 * ([Phone.Type] = 'Home')  
    5 * ([Phone.Type] = 'Work') as priority
  from original),
  tmp2 as (select
      *, 
      rank() over (partition by Account order by priority) as r 
    from tmp),
  p1 as (select * from tmp2 where r = 1),
  p2 as (select * from tmp2 where r = 2)
  select p1.Account, p1.[Phone.Number] No1, p2.[Phone.Number] No2
    from p1
    left join p2 on p1.Account = p2.Account")

giving:

  Account          No1          No2
1       1 222-333-4444 333-444-5555
2       2 555-666-7777 444-555-6666
3       3 666-777-8888 777-888-9999