Search code examples
sqlprestotrino

Pivot in Presto SQL if you don't know the column names in advance

I have a set of records showing the status of implementation of software scomponents. Each row has a feature area, a feature within that area, the product where that feature appears, and its implementation status (green, red, or yellow), like this:

Feature_Area Feature Product Status
User experience Sign Up App1 Green
User experience Sign Up App2 Red
User experience Log off App1 Green
User experience Log off App2 Red
Back End Update User App3 Green
Back End Delete User App3 Red

I'd like to pivot this as shown here: enter image description here

Specifically, I'd like a single row for each feature area and columns for each app to show the status of that feature.

I tried grouping by Feature_Area and then creating columns like this:

COALESCE( ARBITRARY(IF product = 'App1', status, 'N/A' )) AS "App1"

But for my use cases, the app name will change with the data (e.g., I could have App5, App6, etc.) so I can't list the column names in the query.

Solution

  • You need to know the columns. Presto/Trino does not support dynamic pivoting, at least at the moment (also what if you have a lot of different values in Product column). One thing you can do is to use "outside" scripting - read the data and dynamically generate the query.