CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.5unpivot
Abel Garcia
Abel Garcia

Reputation: 178

Convert rows with multiple attributes into rows with one attribute per row

I have a table in the following form:

Id    |   Arrival  |  Departure | Expected Return
-------------------------------------------------
1     | 2019/8/2   | 2019/8/10  | 2019/8/15
2     | 2019/8/1   | 2019/8/15  | 2019/8/22
3     | 2019/8/2   | 2019/8/16  | 2019/8/21

however, I would need a query that returns something like this (ideally without defining additional functions)

Id    |   Action        |  Date
--------------------------------------
1     | Arrival         | 2019/8/2   
1     | Departure       | 2019/8/10  
1     | Expected Return | 2019/8/15
2     | Arrival         | 2019/8/1   
2     | Departure       | 2019/8/15  
2     | Expected Return | 2019/8/22
3     | Arrival         | 2019/8/2   
3     | Departure       | 2019/8/16  
3     | Expected Return | 2019/8/21

Upvotes: 0

Views: 140

Answers (1)

forpas
forpas

Reputation: 164089

With UNION ALL:

select Id, 'Arrival' "Action", Arrival Date from tablename
union all
select Id, 'Departure' "Action", Departure Date from tablename
union all
select Id, 'Expected Return' "Action", "Expected Return" Date from tablename
order by Id, Date

See the demo.
Results:

| id  | Action          | date       |
| --- | --------------- | -----------|
| 1   | Arrival         | 2019-08-02 |
| 1   | Departure       | 2019-08-10 |
| 1   | Expected Return | 2019-08-15 |
| 2   | Arrival         | 2019-08-01 |
| 2   | Departure       | 2019-08-15 |
| 2   | Expected Return | 2019-08-22 |
| 3   | Arrival         | 2019-08-02 |
| 3   | Departure       | 2019-08-16 |
| 3   | Expected Return | 2019-08-21 |

Edit.
Using LATERAL ... VALUES could be more efficient (although not simpler):

select t.id, v.*
from tablename t, 
  lateral (values
    ('Arrival', t.Arrival),
    ('Departure', t.Departure),
    ('Expected Return', t."Expected Return")
   ) v (Action, Date);

See the demo.

Upvotes: 2

Related Questions