1 Answer
- Newest
- Most votes
- Most comments
0
Greetings
Amazon Athena uses Presto SQL, which doesn't have a native PIVOT function. However, you can still achieve the desired result using conditional aggregation. Here's an example query to pivot your table based on the given data structure:
SELECT
MAX(CASE WHEN day = 'monday' THEN CONCAT(openinghour, ' - ', closinghour) END) AS monday,
MAX(CASE WHEN day = 'tuesday' THEN CONCAT(openinghour, ' - ', closinghour) END) AS tuesday,
MAX(CASE WHEN day = 'wednesday' THEN CONCAT(openinghour, ' - ', closinghour) END) AS wednesday
FROM
(SELECT day, openinghour, closinghour, ROW_NUMBER() OVER (PARTITION BY day ORDER BY openinghour) AS row_num
FROM your_table) subquery
GROUP BY row_num;
Replace your_table with the actual name of your table in Athena.
This query first numbers the rows for each day using ROW_NUMBER() function, and then pivots the table by aggregating the rows based on the row_num column using MAX and CASE expressions. The output will display opening and closing hours for each day in separate columns, as requested.
Please let me know if I answered your question
Relevant content
- asked a year ago
- asked 9 months ago
- asked 2 years ago
- asked 2 years ago
- AWS OFFICIALUpdated 3 years ago
- AWS OFFICIALUpdated 22 days ago
- AWS OFFICIALUpdated 3 years ago