sql - 将具有多个属性的行转换为每行具有一个属性的行

Question

我有一个表格，格式如下:

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 

然而，我需要一个返回类似这样的东西的查询(理想情况下不定义额外的函数)

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

Answer 1

联合所有:

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

参见 demo .
结果:

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

编辑。
使用LATERAL ... VALUES 可能更有效(尽管并不简单):

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

参见 demo .