Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE phone(key varchar(4), 
Mobile1 varchar(32),
Mobile2 varchar(32),
M1_St_Date varchar(10),
M2_St_Date varchar(10),
M1_Exp_Date varchar(10),
M2_Exp_Date varchar(10));

INSERT INTO phone VALUES 
('1234','1234567','2234567','2014-01-01','2015-01-01',
'2019-01-01','2020-01-01'), 
('1235','1234568','','2013-01-01','','2018-01-01',''),
('1236','','2234568','','2018-01-01','','2023-01-01');
3 rows affected

SELECT * FROM phone


key
mobile1
mobile2
m1_st_date
m2_st_date
m1_exp_date
m2_exp_date


1234
1234567
2234567
2014-01-01
2015-01-01
2019-01-01
2020-01-01

1235
1234568

2013-01-01

2018-01-01


1236

2234568

2018-01-01

2023-01-01


select t.key, s.* FROM phone t
JOIN LATERAL (VALUES('Personal', t.Mobile1,t.M1_St_Date,t.M1_Exp_Date),('Office', t.Mobile2, t.M2_St_Date, t.M2_Exp_Date)) s(phonetype, phonenumber,startdate,enddate) ON TRUE
WHERE phonenumber != ''


key
phonetype
phonenumber
startdate
enddate


1234
Personal
1234567
2014-01-01
2019-01-01

1234
Office
2234567
2015-01-01
2020-01-01

1235
Personal
1234568
2013-01-01
2018-01-01

1236
Office
2234568
2018-01-01
2023-01-01

key	mobile1	mobile2	m1_st_date	m2_st_date	m1_exp_date	m2_exp_date
1234	1234567	2234567	2014-01-01	2015-01-01	2019-01-01	2020-01-01
1235	1234568		2013-01-01		2018-01-01
1236		2234568		2018-01-01		2023-01-01

key	phonetype	phonenumber	startdate	enddate
1234	Personal	1234567	2014-01-01	2019-01-01
1234	Office	2234567	2015-01-01	2020-01-01
1235	Personal	1234568	2013-01-01	2018-01-01
1236	Office	2234568	2018-01-01	2023-01-01