Unpivot the table

Recently I had an interview and interviewer asked me how I can to rotate the following table:

To rotate means to get the following result (example for shop_id = 1):

I suggested using unpivot and e.g. in Oracle it could be smth like that:

	WITH calendar as (
	SELECT 1 as shop_id, 0 as mon, 0 as tue, 1 as wed, 1 as thur, 1 as fri, 0 as sat, 0 as sun FROM dual
	UNION ALL
	SELECT 2 as shop_id, 1 as mon, 1 as tue, 1 as wed, 1 as thur, 0 as fri, 0 as sat, 0 as sun FROM dual
	)
	SELECT shop_id, dayOfWeek, isWorkDay
	FROM calendar
	UNPIVOT (
	isWorkDay
	FOR dayOfWeek IN (mon, tue, wed, thur, fri, sat, sun)
	)
	WHERE shop_id = 1

view raw Unpivot1.sql hosted with ❤ by GitHub

But I was interrupted and the additional requirement was what would I do if the use of unpivot is prohibited? I proposed to rewrite the sql-query via UNION ALL:

	WITH calendar as (
	SELECT 1 as shop_id, 0 as mon, 0 as tue, 1 as wed, 1 as thur, 1 as fri, 0 as sat, 0 as sun FROM dual
	UNION ALL
	SELECT 2 as shop_id, 1 as mon, 1 as tue, 1 as wed, 1 as thur, 0 as fri, 0 as sat, 0 as sun FROM dual
	)
	SELECT * FROM (
	SELECT shop_id, 'MON' as dayOfWeek, mon as isWorkDay
	FROM calendar
	UNION ALL
	SELECT shop_id, 'TUE' as dayOfWeek, tue as isWorkDay
	FROM calendar
	UNION ALL
	SELECT shop_id, 'WED' as dayOfWeek, wed as isWorkDay
	FROM calendar
	UNION ALL
	SELECT shop_id, 'THUR' as dayOfWeek, thur as isWorkDay
	FROM calendar
	UNION ALL
	SELECT shop_id, 'FRI' as dayOfWeek, fri as isWorkDay
	FROM calendar
	UNION ALL
	SELECT shop_id, 'SAT' as dayOfWeek, sat as isWorkDay
	FROM calendar
	UNION ALL
	SELECT shop_id, 'SUN' as dayOfWeek, sun as isWorkDay
	FROM calendar
	)
	WHERE shop_id = 1

view raw unpivot2.sql hosted with ❤ by GitHub

However, my opponent asked me to figure out one more method via a recursive query. And here it is:

	WITH calendar as (
	SELECT 1 as shop_id, 0 as mon, 0 as tue, 1 as wed, 1 as thur, 1 as fri, 0 as sat, 0 as sun FROM dual
	UNION ALL
	SELECT 2 as shop_id, 1 as mon, 1 as tue, 1 as wed, 1 as thur, 0 as fri, 0 as sat, 0 as sun FROM dual
	)
	, seq as (SELECT level as lvl FROM dual CONNECT BY level <= 7)
	SELECT shop_id,
	case when lvl = 1 then 'mon'
	when lvl = 2 then 'tue'
	when lvl = 3 then 'wed'
	when lvl = 4 then 'thur'
	when lvl = 5 then 'fri'
	when lvl = 6 then 'sat'
	when lvl = 7 then 'sun'
	end as dayOfWeek,
	case when lvl = 1 then mon
	when lvl = 2 then tue
	when lvl = 3 then wed
	when lvl = 4 then thur
	when lvl = 5 then fri
	when lvl = 6 then sat
	when lvl = 7 then sun
	end as isWorkDay
	FROM calendar, seq
	WHERE shop_id = 1
	ORDER BY shop_id, lvl

view raw Unpivot3.sql hosted with ❤ by GitHub

And in general using queries above you can get the following result: