How do write an SQL query that transforms phone numbers from columns into a single column?

Question

How do I write an SQL query that transforms phone numbers from columns into a single column. Assume there are multiple personIDs and each personID has up to 3 phone types, Primary, Secondary and Tertiary. Currently, for each person, they are listed in three separate columns.

The desired outcome is that the phone numbers are all in one column, and another column has the phone types

Current Data

Person_ID	Primary_Phone	Secondary_Phone	Tertiary_Phone
1	2221111111	5551111111	9991111111
2	2221111112	5551111112	9991111112
3	2221111113	5551111113	9991111113
4	2221111114	5551111114	9991111114

Desired Data

Person_ID	Phone_Number	Phone_Type
1	2221111111	Primary
1	5551111111	Secondary
1	9991111111	Tertiary
2	2221111112	Primary
2	5551111112	Secondary
2	9991111112	Tertiary
3	2221111113	Primary
3	5551111113	Secondary
3	9991111113	Tertiary
4	2221111114	Primary
4	5551111114	Secondary
4	9991111114	Tertiary

score 3 · Answer 1 · answered Dec 30 '20 at 23:24

In Oracle starting version 12c, you can unpivot the columns to rows with cross apply:

select t.person_id, x.*
from mytable t
cross apply (
    select primary_phone as phone_number, 'Primary' as phone_type from dual
    union all select secondary_phone, 'Secondary' from dual
    union all select tertiary_phone, 'Tiertiary' from dual
) x

In earlier versions, you could use union all:

select person_id, primary_phone as phone_number, 'Primary' as phone_type from mytable
union all select person_id, secondary_phone, 'Secondary' from mytable
union all select person_id, tertiary_phone, 'Tiertiary' from mytable

Thanks a lot. Either option would certainly help – cmomah Dec 31 '20 at 20:33 — cmomah, Dec 31 '20 at 20:33

score 2 · Accepted Answer · answered Dec 30 '20 at 23:48

You appear to want to do an unpivot

with p as (
  select 1 person_id, 
         '2221111111' primary_phone, 
         '5551111111' secondary_phone, 
         '9991111111' tertiary_phone
    from dual
  union all
  select 2, 
         '2221111112' primary_phone, 
         '5551111112' secondary_phone, 
         '9991111112' tertiary_phone
    from dual
)
select person_id,
       phone_number,
       phone_type
  from p
unpivot (
  phone_number
  for phone_type in (
     primary_phone as 'Primary',
     secondary_phone as 'Secondary',
     tertiary_phone as 'Tertiary'
  )
)

A liveSQL link showing the query running

How do write an SQL query that transforms phone numbers from columns into a single column?

2 Answers2

Linked

Related