0

I'm a relative newbie to MySQL. I have experimented with selects and joins to filter and combine data from different tables. One thing I'm struggling with is how to output more than one line for one row.

Here is an example I've created to describe what I'm trying to do. A person can have 0 to 3 phone numbers. 

ID     First  Last  Bus Phone    Home Phone   Mobile Phone

40550  Ed     Zo    555-145-7424 333-743-1233 123-456-7890
46476  Rui    Jun   234-567-8901 345-678-9012   
26480 Matt    Del                             222-333-4444

I would like to create 1 row of output for each phone number the person has.

ID      First   Last    PhoneType           Number
40550   Ed    Zo          B             555-145-7424
40550   Ed    Zo          H             333-743-1234
40550   Ed    Zo          M             123-456-7890
46476   Rui   Jun         B             234-567-8901
46476   Rui   Jun         H             345-678-9012
26480   Matt  Del         M             222-333-4444

What SQL statements should I be looking at? Any pointers would be greatly appreciated.

Thank you!

MG

MLG
  • 3
  • 1
  • See about PIVOT – Strawberry Feb 14 '19 at 23:48
  • "See about PIVOT " @Strawberry isn't this unpivotting.. He want to go from columns to records. PIVOT is from records to columns right? – Raymond Nijland Feb 14 '19 at 23:55
  • 1
    @raymondnijland just checking you were paying attention – Strawberry Feb 14 '19 at 23:57
  • You should be looking at examples on Google. Come here when you have some code that's not working as it should after you've followed some basic tutorials and learned the basics. StackOverflow has some fairly strict guidelines about what kinds of questions can be asked here, otherwise it defeats much of the value and way people use the site. – clearlight Feb 15 '19 at 00:00

1 Answers1

2

In MySQL, the simplest approach is union all:

select id, first, last, 'B' as phoneType, bus_phone
from t
where bus_phone is not null
union all
select id, first, last, 'h', home_phone
from t
where home_phone is not null
union all
select id, first, last, 'M', mobile_phone
from t
where mobile_phone is not null;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I discovered that my data didn't have null field because I was seeing blank cells in the report. I modified the query to select id, first, last, 'M', mobile_phone from t where not (phone_M is null or mobile_phone = ''); That did the trick. Thanks again, Gordon! – MLG Feb 15 '19 at 02:32