Reformatting results, Turning Columns to Rows, MYSQL

Question

My coworker wrote an awesome query that solves for discreet hours with a situation in which people are logging-in and out continually, so looking for how many 'supply hours' were available at 10 AM today may be tricky since someone may not have logged out. So, prior to this query, we could see things in aggregate at the day, weekly, month level, but not really hourly. The Query is at the end of the question

And here are what the results look like:

Query Results IDE

You'll see the hourly columns extend to hour 23. What I need in order to make joins work in Tableau, is to have results that show: col 1 Market, col 2 Date, col 3 Hour, col 4 Supply Hour Sum ATL, ,1-3-2022, 3 , 12 ATL ,1-3-2022, 4 , 4 ATL ,1-3-2022, 5 , 7

And so on for all entries currently spread out in the hours columns. I'm fairly new to sql and any help would be much appreciated.

Thank you,

Hadden

DATA.market_name,
# DATA.driver_id,
DATA.ONDUTY_DATE AS DATE,
SUM(HOUR_0_SAME_DAY) AS '0',
SUM(HOUR_1_SAME_DAY) AS '1',
SUM(HOUR_2_SAME_DAY) AS '2',
SUM(HOUR_3_SAME_DAY) AS '3',
SUM(HOUR_4_SAME_DAY) AS '4',
SUM(HOUR_5_SAME_DAY) AS '5',
SUM(HOUR_6_SAME_DAY) AS '6',
SUM(HOUR_7_SAME_DAY) AS '7',
SUM(HOUR_8_SAME_DAY) AS '8',
SUM(HOUR_9_SAME_DAY) AS '9',
SUM(HOUR_10_SAME_DAY) AS '10',
SUM(HOUR_11_SAME_DAY) AS '11',
SUM(HOUR_12_SAME_DAY) AS '12',
SUM(HOUR_13_SAME_DAY) AS '13',
SUM(HOUR_14_SAME_DAY) AS '14',
SUM(HOUR_15_SAME_DAY) AS '15',
SUM(HOUR_16_SAME_DAY) AS '16',
SUM(HOUR_17_SAME_DAY) AS '17',
SUM(HOUR_18_SAME_DAY) AS '18',
SUM(HOUR_19_SAME_DAY) AS '19',
SUM(HOUR_20_SAME_DAY) AS '20',
SUM(HOUR_21_SAME_DAY) AS '21',
SUM(HOUR_22_SAME_DAY) AS '22',
SUM(HOUR_23_SAME_DAY) AS '23'

FROM
(SELECT
SUPPLYHRS.driver_id,
MARKET.market_name,
DATE(SUPPLYHRS.onduty_time) AS ONDUTY_DATE,

CASE WHEN 0 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_0_SAME_DAY',
CASE WHEN 1 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_1_SAME_DAY',
CASE WHEN 2 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_2_SAME_DAY',
CASE WHEN 3 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_3_SAME_DAY',
CASE WHEN 4 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_4_SAME_DAY',
CASE WHEN 5 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_5_SAME_DAY',
CASE WHEN 6 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_6_SAME_DAY',
CASE WHEN 7 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_7_SAME_DAY',
CASE WHEN 8 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_8_SAME_DAY',
CASE WHEN 9 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_9_SAME_DAY',
CASE WHEN 10 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_10_SAME_DAY',
CASE WHEN 11 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_11_SAME_DAY',
CASE WHEN 12 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_12_SAME_DAY',
CASE WHEN 13 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_13_SAME_DAY',
CASE WHEN 14 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_14_SAME_DAY',
CASE WHEN 15 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_15_SAME_DAY',
CASE WHEN 16 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_16_SAME_DAY',
CASE WHEN 17 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_17_SAME_DAY',
CASE WHEN 18 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_18_SAME_DAY',
CASE WHEN 19 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_19_SAME_DAY',
CASE WHEN 20 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_20_SAME_DAY',
CASE WHEN 21 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_21_SAME_DAY',
CASE WHEN 22 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_22_SAME_DAY',
CASE WHEN 23 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_23_SAME_DAY'


FROM
janus_service_driver.t_driver_duty_log SUPPLYHRS

LEFT JOIN
janus_service_driver.t_driver_area_market MARKET

ON
SUPPLYHRS.driver_id = MARKET.Driver_ID

WHERE
DATE(SUPPLYHRS.onduty_time) BETWEEN DATE('2022-01-01') AND DATE(SYSDATE()) AND
SUPPLYHRS.offduty_time IS NOT NULL

GROUP BY
SUPPLYHRS.driver_id) DATA

WHERE
market_name IS NOT NULL

GROUP BY
DATA.market_name,
# DATA.driver_id,
DATA.ONDUTY_DATE

UNION

SELECT
DATA.market_name,
# DATA.driver_id,
DATE_ADD(DATA.ONDUTY_DATE,interval 1 day) AS "DATE",
SUM(HOUR_0_NEXT_DAY) AS '0',
SUM(HOUR_1_NEXT_DAY) AS '1',
SUM(HOUR_2_NEXT_DAY) AS '2',
SUM(HOUR_3_NEXT_DAY) AS '3',
SUM(HOUR_4_NEXT_DAY) AS '4',
SUM(HOUR_5_NEXT_DAY) AS '5',
SUM(HOUR_6_NEXT_DAY) AS '6',
SUM(HOUR_7_NEXT_DAY) AS '7',
SUM(HOUR_8_NEXT_DAY) AS '8',
SUM(HOUR_9_NEXT_DAY) AS '9',
SUM(HOUR_10_NEXT_DAY) AS '10',
SUM(HOUR_11_NEXT_DAY) AS '11',
SUM(HOUR_12_NEXT_DAY) AS '12',
SUM(HOUR_13_NEXT_DAY) AS '13',
SUM(HOUR_14_NEXT_DAY) AS '14',
SUM(HOUR_15_NEXT_DAY) AS '15',
SUM(HOUR_16_NEXT_DAY) AS '16',
SUM(HOUR_17_NEXT_DAY) AS '17',
SUM(HOUR_18_NEXT_DAY) AS '18',
SUM(HOUR_19_NEXT_DAY) AS '19',
SUM(HOUR_20_NEXT_DAY) AS '20',
SUM(HOUR_21_NEXT_DAY) AS '21',
SUM(HOUR_22_NEXT_DAY) AS '22',
SUM(HOUR_23_NEXT_DAY) AS '23'

FROM
(SELECT
SUPPLYHRS.driver_id,
MARKET.market_name,
DATE(SUPPLYHRS.onduty_time) AS ONDUTY_DATE,

CASE WHEN 0 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_0_NEXT_DAY',
CASE WHEN 1 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_1_NEXT_DAY',
CASE WHEN 2 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_2_NEXT_DAY',
CASE WHEN 3 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_3_NEXT_DAY',
CASE WHEN 4 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_4_NEXT_DAY',
CASE WHEN 5 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_5_NEXT_DAY',
CASE WHEN 6 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_6_NEXT_DAY',
CASE WHEN 7 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_7_NEXT_DAY',
CASE WHEN 8 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_8_NEXT_DAY',
CASE WHEN 9 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_9_NEXT_DAY',
CASE WHEN 10 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_10_NEXT_DAY',
CASE WHEN 11 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_11_NEXT_DAY',
CASE WHEN 12 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_12_NEXT_DAY',
CASE WHEN 13 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_13_NEXT_DAY',
CASE WHEN 14 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_14_NEXT_DAY',
CASE WHEN 15 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_15_NEXT_DAY',
CASE WHEN 16 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_16_NEXT_DAY',
CASE WHEN 17 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_17_NEXT_DAY',
CASE WHEN 18 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_18_NEXT_DAY',
CASE WHEN 19 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_19_NEXT_DAY',
CASE WHEN 20 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_20_NEXT_DAY',
CASE WHEN 21 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_21_NEXT_DAY',
CASE WHEN 22 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_22_NEXT_DAY',
CASE WHEN 23 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_23_NEXT_DAY'

FROM
janus_service_driver.t_driver_duty_log SUPPLYHRS

LEFT JOIN
janus_service_driver.t_driver_area_market MARKET

ON
SUPPLYHRS.driver_id = MARKET.Driver_ID

WHERE
DATE(SUPPLYHRS.onduty_time) BETWEEN DATE('2022-01-01') AND DATE(SYSDATE()) AND
SUPPLYHRS.offduty_time IS NOT NULL

GROUP BY
SUPPLYHRS.driver_id) DATA

WHERE
market_name IS NOT NULL

GROUP BY
DATA.market_name,
# DATA.driver_id,
DATA.ONDUTY_DATE