SQL to transpose and create rows based on column values

Question

I have data in Postgres SQL, something like this

|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 |

I am trying to create something like below

|  KEY  | Type    | Mobile Number|St Date   |Exp Date  |
| ------| --------| -----------  |--------  |----------|
| 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|

Mobile 1 should be named as Personal & Mobile2 should be named as Office under Type Column

Appreciate any help!

You can look at how to [unpivot in postgresql](https://stackoverflow.com/questions/1128737/equivalent-to-unpivot-in-postgresql) — Halim Saad-Rached, Aug 30 '22 at 16:34
Thanks-I have used unnest function , and it is working as per my expectations. — datatester, Aug 30 '22 at 20:27

Halim Saad-Rached · Accepted Answer · 2022-08-31T23:17:50.183

0

SELECT 
t.key, 
phonetype, 
phonenumber,
startdate, 
enddate
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

db fiddle

edited Aug 31 '22 at 23:17

answered Aug 31 '22 at 22:49

Halim Saad-Rached

154
1
9

SQL to transpose and create rows based on column values

1 Answers1