How to Pivot an AWS Athena table via SQL

Q&A
, ,
1

I am trying to digest data to give me a visual in the following format

Location ID Type Metric This Month Last Month MoM % Change
12345 A Metric 1 45 23 48.88%
12345 B Metric 1 54 52 3.85%
67890 A Metric 1 74 89 -20.27%
67890 B Metric 1 23 26 -13.04%
65432 A Metric 1 76 63 17.11%
65432 B Metric 1 45 23 48.88%
12345 A Metric 2 54 52 3.85%
12345 B Metric 2 74 89 -20.27%
67890 A Metric 2 23 26 -13.04%
67890 B Metric 2 76 63 17.11%
65432 A Metric 2 54 52 3.85%
65432 B Metric 2 74 89 -20.27%

My data is currently in this format:

Location ID Type Date Metric1 Metric2 Metric3 Metric4 MetricN
12345 A 08/31/2025 45 54 36 23 65
12345 B 08/31/2025 54 74 44 32 74
67890 A 8/31/2025 74 23 66 34 36
67890 B 8/31/2025 23 76 47 46 27
12345 A 7/31/2025 23 52 42 78 12
12345 B 7/31/2025 52 89 78 45 68
67890 A 7/31/2025 89 26 78 35 36
67890 B 7/31/2025 26 63 38 26 94

Data above is randomized dummy data, but all data will be whole numbers.

I am trying to figure out a way to flip my metric columns into rows but am having issues figuring out a simple way to do so. I want to have a “Metric Name” column that contains the metric name and then a “This Month” and “Last Month” column so I can use those values to calculate the MoM % change.

I was looking into pivoting the table with map_agg() but that was also causing issues because the Date field is the best key I have for map_agg() and that would lead to the key being a dynamic value instead of a static value. It also didn’t give me an easy way to create that “Metric Name” column.

I’ve done this before but I cannot remember how I did it so any help is appreciated.

2

Hi @hcorbett ,

this example SQL code may help you:

SELECT
Dim1,
Dim2,
Dim3,
‘Metric1’ AS metric-name,
Metric 1 AS ‘This Month‘
FROM your_table

UNION ALL

SELECT
Dim1,
Dim2,
Dim3,
‘Metric2’ AS metric-name,
Metric 2 AS ‘This Month‘
FROM your_table

UNION ALL

SELECT
Dim1,
Dim2,
Dim3,
‘Metric3’ AS metric-name,
Metric 3 AS ‘This Month‘
FROM your_table

Once you have pivoted your table you can start calculation the previos month value.

Does this help?

Best regards,

Nico

3

That is what I tried, but because my query uses a user selected parameter, it ends up timing out in the visual

4

Do you use SPICE to ingest the data into Quick Sight?

If not, maybe this link helps: Importing data into SPICE - Amazon QuickSight

Best regards,

Nico

5

Because I use a user selected parameter in my query, I have to use Direct Query

6

Can you prepare the table in athena in the way you need it? Otherwise the data transformation (union) needs to be done live. That needs time and ends up in the time out.

My suggestion is to transform the data in your table instead of using the custom sql.

Best regards,

Nico

7

That is likely what I’ll have to end up doing. Hopefully I can make the query semi-efficient

1 Like
8

@hcorbett (cc: @Nico ) Have you tried the new data preparation experience in Quick Sight that was recently launched? It now provides Pivot, Unpivot, Append, Aggregate (ListAgg, ListAgg Distinct) and many more features in data preparation through a visual interface.

1 Like