UNPIVOT in sql server without Aggregate

Question

I have a Table As below.

OfficeID    SunFrom SunTo   MonFrom MonTo   TueFrom TueTo   WedFrom WedTo   ThuFrom ThuTo   FriFrom FriTo   SatFrom SatTo
51834       12      17      8       22      8       22      8       22      8       22      9       21      8       19

I need to convert this table's column's into rows

Expected output:

Officeid    Day         Daystart    DayEnd

51834       Sunday      12          17
51834       Monday      8           22
51834       Tuesday     8           22
51834       Wednesday   8           22
51834       Thursday    8           22
51834       Friday      9           21
51834       Saturday    8           19

I tried with UNPIVOT but i am not sure which column I need to choose as a pivot column. Please help....

Answer 1

This gets most of the way there. If you want a specific output order, I'd suggest using an auxiliary table that maps the 3 character day names to a sorting index (and the full length name, if desired):

declare @t table (OfficeID int,SunFrom  int,SunTo int,MonFrom int,MonTo int,
TueFrom int,TueTo int,WedFrom int,WedTo int,
ThuFrom int,ThuTo int,FriFrom int,FriTo int,
SatFrom int,SatTo int)
insert into @t(OfficeID,SunFrom,SunTo,MonFrom,MonTo,TueFrom,TueTo,
WedFrom,WedTo,ThuFrom,ThuTo,FriFrom,FriTo,SatFrom,SatTo) values
(51834,12,17,8,22,8,22,8,22,8,22,9,21,8,19)

select
    OfficeID,Day,
        MAX(CASE WHEN Endpoint='From' THEN EndpointTime END) as Daystart,
        MAX(CASE WHEN Endpoint='To' THEN EndpointTime END) as DayEnd
from
    @t t
        unpivot
    (EndpointTime for DayAndEndPoint in (SunFrom,SunTo,MonFrom,MonTo,TueFrom,TueTo,
WedFrom,WedTo,ThuFrom,ThuTo,FriFrom,FriTo,SatFrom,SatTo)) a
        cross apply
    (select SUBSTRING(DayAndEndpoint,1,3) as Day,SUBSTRING(DayAndEndpoint,4,4) as Endpoint) b
group by
    OfficeID,Day

Result:

OfficeID    Day  Daystart    DayEnd
----------- ---- ----------- -----------
51834       Fri  9           21
51834       Mon  8           22
51834       Sat  8           19
51834       Sun  12          17
51834       Thu  8           22
51834       Tue  8           22
51834       Wed  8           22

As you're hopefully aware, if possible it would be better to redesign your database to more closely resemble this result set than the original - data of the same "type" ought to be in a single column, and data ought to be modelled as data rather than embedded in column names.

---

Extended variant if output order and day names are specifically important:

declare @t table (OfficeID int,SunFrom  int,SunTo int,MonFrom int,MonTo int,
TueFrom int,TueTo int,WedFrom int,WedTo int,
ThuFrom int,ThuTo int,FriFrom int,FriTo int,
SatFrom int,SatTo int)
insert into @t(OfficeID,SunFrom,SunTo,MonFrom,MonTo,TueFrom,TueTo,
WedFrom,WedTo,ThuFrom,ThuTo,FriFrom,FriTo,SatFrom,SatTo) values
(51834,12,17,8,22,8,22,8,22,8,22,9,21,8,19)

declare @DayNameAndSort table (Day varchar(3) not null,ExtendedName varchar(19) not null,SortOrder int not null)
insert into @DayNameAndSort (Day,ExtendedName,SortOrder) values
('Fri','Friday',   5),
('Mon','Monday',   1),
('Sat','Saturday', 6),
('Sun','Sunday',   0),
('Thu','Thursday', 4),
('Tue','Tuesday',  2),
('Wed','Wednesday',3)


;With ReOriented as (
select
    OfficeID,Day,
        MAX(CASE WHEN Endpoint='From' THEN EndpointTime END) as Daystart,
        MAX(CASE WHEN Endpoint='To' THEN EndpointTime END) as DayEnd
from
    @t t
        unpivot
    (EndpointTime for DayAndEndPoint in (SunFrom,SunTo,MonFrom,MonTo,TueFrom,TueTo,
WedFrom,WedTo,ThuFrom,ThuTo,FriFrom,FriTo,SatFrom,SatTo)) a
        cross apply
    (select SUBSTRING(DayAndEndpoint,1,3) as Day,SUBSTRING(DayAndEndpoint,4,4) as Endpoint) b
group by
    OfficeID,Day
)
select
    OfficeID,ExtendedName,Daystart,DayEnd
from
    ReOriented r
        inner join
    @DayNameAndSort s
        on
            r.Day = s.Day
order by s.SortOrder