CODEWITHSUNDEEP
sql-serverprestoamazon-athena
Salman Tahir
Salman Tahir

Reputation: 47

CROSS APPLY function in Presto/AWS

I'm trying to convert 4 date columns titled Created, Approved, Processed & Realized to a single column with all 4 dates AND a second column with the status of each of those dates.

The image at the end shows the data issue visually (apologies I'm still figuring out how to attach tables in textual form on stackoverflow)

To solve this, I successfully executed the CROSS APPLY function in SQL server (see below), but I now need to do the same in AWS Simba Athena or the Presto language. Can someone please guide me on what is the AWS/Presto equivalent of a CROSS APPLY function? Thank you in advance

SELECT 

    V.Date,
    V.Status
    From Table C
    CROSS APPLY     
        (VALUES 
                (C.Created, 'Opened'), 
                (C.Approved, 'Approved'), 
                (C.Processed, 'Processed'), 
                (C.Realized, 'Realized')
                                                 ) AS V([Date], Status)

I want to convert the following table:

enter image description here

Upvotes: 1

Views: 3651

Answers (1)

Piotr Findeisen
Piotr Findeisen

Reputation: 20770

You should be able to use UNNEST for this:

SELECT v.date, v.status
FROM m_table
CROSS JOIN UNNEST(ARRAY[
 ROW(C.Created, 'Opened'), 
 ROW(C.Approved, 'Approved'), 
 ROW(C.Processed, 'Processed'), 
 ROW(C.Realized, 'Realized')
]) AS v(date, status);

This works in the latest Presto version, 337.

In Athena you probably still cannot UNNEST array or ROW in ANSI SQL manner, so you may need some modifications.

Upvotes: 3

Related Questions