# How to Pivot an AWS Athena table via SQL **URL:** \ **Category:** Q&A\ **Tags:** qls-community, developer, quick-sight\ **Created:** [September 22, 2025, 5:34pm UTC](https://community.amazonquick.com/t/how-to-pivot-an-aws-athena-table-via-sql/49785 "2025-09-22T17:34:46Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![hcorbett](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@hcorbett](https://community.amazonquick.com/u/hcorbett)\ **Post date:** [September 22, 2025, 5:34pm UTC](https://community.amazonquick.com/t/how-to-pivot-an-aws-athena-table-via-sql/49785/1 "2025-09-22T17:34:46Z")
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. ---
**Author:** ![Nico](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/nico/32/32113_2.png) [@Nico](https://community.amazonquick.com/u/Nico)\ **Post date:** [September 23, 2025, 6:59am UTC](https://community.amazonquick.com/t/how-to-pivot-an-aws-athena-table-via-sql/49785/2 "2025-09-23T06:59:10Z")
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 ---
**Author:** ![hcorbett](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@hcorbett](https://community.amazonquick.com/u/hcorbett)\ **Post date:** [September 23, 2025, 1:04pm UTC](https://community.amazonquick.com/t/how-to-pivot-an-aws-athena-table-via-sql/49785/3 "2025-09-23T13:04:05Z")
That is what I tried, but because my query uses a user selected parameter, it ends up timing out in the visual ---
**Author:** ![Nico](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/nico/32/32113_2.png) [@Nico](https://community.amazonquick.com/u/Nico)\ **Post date:** [September 25, 2025, 6:14am UTC](https://community.amazonquick.com/t/how-to-pivot-an-aws-athena-table-via-sql/49785/4 "2025-09-25T06:14:59Z")
Do you use SPICE to ingest the data into Quick Sight? If not, maybe this link helps: [Importing data into SPICE - Amazon QuickSight](https://docs.aws.amazon.com/quicksight/latest/user/spice.html) Best regards, Nico ---
**Author:** ![hcorbett](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@hcorbett](https://community.amazonquick.com/u/hcorbett)\ **Post date:** [September 25, 2025, 1:01pm UTC](https://community.amazonquick.com/t/how-to-pivot-an-aws-athena-table-via-sql/49785/5 "2025-09-25T13:01:05Z")
Because I use a user selected parameter in my query, I have to use Direct Query ---
**Author:** ![Nico](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/nico/32/32113_2.png) [@Nico](https://community.amazonquick.com/u/Nico)\ **Post date:** [September 26, 2025, 6:21am UTC](https://community.amazonquick.com/t/how-to-pivot-an-aws-athena-table-via-sql/49785/6 "2025-09-26T06:21:05Z")
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 ---
**Author:** ![hcorbett](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@hcorbett](https://community.amazonquick.com/u/hcorbett)\ **Post date:** [September 26, 2025, 6:33pm UTC](https://community.amazonquick.com/t/how-to-pivot-an-aws-athena-table-via-sql/49785/7 "2025-09-26T18:33:07Z")
That is likely what I’ll have to end up doing. Hopefully I can make the query semi-efficient ---
**Author:** ![vignessh.b](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/vignessh.b/32/13620_2.png) [@vignessh.b](https://community.amazonquick.com/u/vignessh.b)\ **Post date:** [November 4, 2025, 2:51am UTC](https://community.amazonquick.com/t/how-to-pivot-an-aws-athena-table-via-sql/49785/8 "2025-11-04T02:51:38Z")
@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. > [@Amazon Quick Sight announces the general availability of a new data preparation experience](https://community.amazonquick.com/t/amazon-quick-sight-announces-the-general-availability-of-a-new-data-preparation-experience/50283): > > Amazon Quick Sight, a capability of Amazon Quick Suite, now offers a visual data preparation experience that helps business users perform advanced data transformations without writing complex code. Users can now clean, transform, and combine data in multi-step workflows—appending tables, aggregating data, executing flexible joins, and other advanced operations that previously required custom programming or SQL commands. Users can easily track data transformations step-by-step, enhancing trace…