CODEWITHSUNDEEP
sqlpivotcrosstabpresto
Bruce Wayne
Bruce Wayne

Reputation: 491

SQL: Crosstab equivalent in Presto

I have a table which looks like this:

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

I want to transform it to look like this:

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

I know in postgres we could use crosstab and make this happen. However, I am unable to do it with Presto. Is there a way I could do this?

Upvotes: 0

Views: 804

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271211

You can use conditional aggregation. If you know all the stations, it looks like:

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;

Otherwise, you'll need to construct the query dynamically to do pretty much the same thing, but with a variable number of columns.

Upvotes: 1

Related Questions