Pivoting Days/Hours in SQL Server From Header to Row Level

I have a list of hours that are under headings from and to. I need to Pivot those to be by days with from and to headings. I’ve tried unpivoting in Excel and Pivot in SQL Server, nothing is working. Each day is corresponded to a SEQ_NO (Monday = 10, Tuesday = 20, etc.). The main problem here is getting the start and end times to be in a different view. Below is the before and after which I want it to look like. Below is Before and After (what I want it to look like).

What I want it to look like
After View (what I want it to look like):

Before:

Name   ║ ID2       ║ Mon Hour Start ║ Mon Hour End ║ Tues Hour Start ║ Tues Hour End ║ Wed Hour Start ║ Wed Hour End ║ Thurs Hour Start ║ Thurs Hour End ║ Fri Hour Start ║ Fri Hour End ║ Sat Hour Start ║ Sat Hour End ║ Sun Hour Start ║ Sun Hour End ║ 
╠══════╬═══════════╬════════════════╬══════════════╬═════════════════╬═══════════════╬════════════════╬══════════════╬══════════════════╬════════════════╬════════════════╬══════════════╬════════════════╬══════════════╬════════════════╬══════════════╣
║ John ║ 123456789 ║ 07:45 AM       ║ 11:59 PM     ║ 07:45 AM        ║ 11:59 PM      ║ 07:45 AM       ║ 11:59 PM     ║ 07:45 AM         ║ 11:59 PM       ║ 07:45 AM       ║ 11:59 PM     ║                ║              ║                ║              ║

After:

╔════════╦══════════════╦═══════════╦══════════╦══╦══╦══╦══╦══╦══╦══╦══╦══╦══╦══╦══╦══╗
║ SEQ_NO ║ DAYS         ║ DAYS_FROM ║ DAYS_TO  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║
╠════════╬══════════════╬═══════════╬══════════╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╣
║ 10     ║ Monday       ║ 7:45 AM   ║ 11:59 PM ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║
╠════════╬══════════════╬═══════════╬══════════╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╣
║ 20     ║ Tuesday      ║ 7:45 AM  ║ 11:59 PM ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║
╠════════╬══════════════╬═══════════╬══════════╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╣
║ 30     ║ Wednesday    ║ 7:45 AM  ║ 11:59 PM ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║
╠════════╬══════════════╬═══════════╬══════════╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╣
║ 40     ║ Thursday     ║ 7:45 AM  ║ 11:59 PM ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║
╠════════╬══════════════╬═══════════╬══════════╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╣
║ 50     ║ Friday       ║ 7:45 AM  ║ 11:59 PM ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║
╠════════╬══════════════╬═══════════╬══════════╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╣
║ 60     ║ Saturday     ║   ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║
╠════════╬══════════════╬═══════════╬══════════╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╬══╣
║ 70     ║ Sunday       ║   ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║  ║
╚════════╩══════════════╩═══════════╩══════════╩══╩══╩══╩══╩══╩══╩══╩══╩══╩══╩══╩══╩══╝

Answer

I believe you could use UNPIVOT:

SELECT [ID2]
, [SEQ_NO] = case 
    when DaysF like 'Mon%' then 10
    when DaysF like 'Tues%' then 20
    when DaysF like 'Wed%' then 30
    when DaysF like 'Thurs%' then 40
    when DaysF like 'Fri%' then 50
    when DaysF like 'Sat%' then 60
    when DaysF like 'Sun%' then 70 end
, [DAYS] = case 
    when DaysF like 'Mon%' then 'Monday'
    when DaysF like 'Tues%' then 'Tuesday'
    when DaysF like 'Wed%' then 'Wednesday'
    when DaysF like 'Thurs%' then 'Thursday'
    when DaysF like 'Fri%' then 'Friday'
    when DaysF like 'Sat%' then 'Saturday'
    when DaysF like 'Sun%' then 'Sunday' end
, [DAYS_FROM], [DAYS_TO] FROM   
(
    SELECT 
        [Name], [ID2]
        , [Mon Hour Start] as [Mon Hour Start1], [Mon Hour End] as [Mon Hour End1], [Tues Hour Start] as [Tues Hour Start2]
        , [Tues Hour End] as [Tues Hour End2], [Wed Hour Start] as [Wed Hour Start3], [Wed Hour End] as [Wed Hour End3]
        , [Thurs Hour Start] as [Thurs Hour Start4], [Thurs Hour End] as [Thurs Hour End4], [Fri Hour Start] as [Fri Hour Start5]
        , [Fri Hour End] as [Fri Hour End5], [Sat Hour Start] as [Sat Hour Start6], [Sat Hour End] as [Sat Hour End6]
        , [Sun Hour Start] as [Sun Hour Start7], [Sun Hour End] as [Sun Hour End7]
    FROM 
        @table
) t 
UNPIVOT 
(
  [DAYS_FROM] FOR [DaysF] IN ([Mon Hour Start1], [Tues Hour Start2], [Wed Hour Start3], [Thurs Hour Start4], [Fri Hour Start5], [Sat Hour Start6], [Sun Hour Start7])
) AS df
UNPIVOT 
(
  [DAYS_TO] FOR [DaysT] IN ([Mon Hour End1], [Tues Hour End2], [Wed Hour End3], [Thurs Hour End4], [Fri Hour End5], [Sat Hour End6], [Sun Hour End7])
) AS dt
where right([DaysF],1) = right([DaysT],1)
;

Advertisement

Answer