SQL: Presto中的交叉表等效项

Question

我有一张表，看起来像这样：

shop    number  work    station accept  create  no.of entries
1   123 Maintain    Arrive  2019-10-11 0:00:00  2019-10-11 0:00:00  1
1   123 Maintain    WorkA   2019-10-11 0:00:00  2019-10-11 0:00:00  1
1   123 Maintain    WorkB   2019-10-11 0:00:00  2019-10-11 0:00:00  1
1   1234    Job Arrive  2019-10-11 0:00:00  2019-10-11 0:00:00  1
1   1234    Job WorkC   2019-10-11 0:00:00  2019-10-11 0:00:00  1
1   1234    Job WorkD   2019-10-11 0:00:00  2019-10-11 0:00:00  1

我想让它看起来像这样：

shop    number  work    arrival_accept  arrival_create  worka_accept    worka_create    workb_accept    workb_create    workc_accept    workc_create    workd_accept    workd_create    no.of entries
1   123 Maintain    2019-10-11 0:00:00  2019-10-11 0:00:00  2019-10-11 0:00:00  2019-10-11 0:00:00  2019-10-11 0:00:00  2019-10-11 0:00:00                  1
1   1234    Job 2019-10-11 0:00:00                      2019-10-11 0:00:00  2019-10-11 0:00:00  2019-10-11 0:00:00  2019-10-11 0:00:00  1

我知道在postgres中我们可以使用交叉表来实现这一点。然而，我不能用Presto做到这一点。有没有办法让我这么做？

Answer 1

您可以使用条件聚合。如果你知道所有的电台，看起来是这样的：

select shop, number, work,
       max(case when station = 'Arrive' then accept end) as arrive_accept,
       max(case when station = 'Arrive' then create end) as arrive_create,
       max(case when station = 'WorkA' then accept end) as workA_accept,
       max(case when station = 'WorkA' then create end) as workA_create,
       . . .  -- and so on for the rest of the stations
from t
group by shop, number, work;

否则，您将需要动态构造查询来做几乎相同的事情，但是使用可变数量的列。