Exploding column by delimiter and using prefix of split column name as value

Question

I have a table with the following aggregation

pageurl	hour01_visits	hour02_visits
abc.com	9000	9500
def.com	3000	2300

And would like to explode it like so

pageurl	hour	visits
abc.com	01	9000
abc.com	02	9500
def.com	01	3000
def.com	02	2300

I know how to explode arrays with Presto cross join unnest but here I am exploding the columns by their name themselves. Not sure if this is possible?

Answer 1

You can either use a classical approach, where you reverse the pivot operation using the UNION ALL operators for all of your columns

SELECT pageurl, 
       1             AS hour,
       hour01_visits AS visits
FROM tab
UNION ALL 
SELECT pageurl, 
       2             AS hour,
       hour02_visits AS visits
FROM tab
ORDER BY pageurl, hour

Yet a smarter option could be to convert your columns into a single array, and apply the UNNEST operation onto it, using the WITH ORDINALITY clause to gather the ordered ids.

SELECT tab.pageurl, hour, visits
FROM tab,
     UNNEST(ARRAY[hour01_visits, hour02_visits]) WITH ORDINALITY cte(visits, hour);

"Output":

pageurl	hour	visits
abc.com	1	9000
abc.com	2	9500
def.com	1	3000
def.com	2	2300

Check the demo here.