Can someone show me how to pivot in Athena SQL?

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