Sql-server – Split a row into two for attendance seet

sql serverstring-splitting

I'm redesigning an attendance system used in my company. Originally, an attendance sheet was rendered on a Grid that was getting week-by-week data Pivoted. I've opted for a Scheduler control by DevExpress. A sign-in and a sign-out are tow be represented as two 'appointments' now.

The Scheduler requires a very specific data source for appointments. Here is my attendance table:

Attendace System Backend

The DutyIn_Manual is what the employee reports and the DutyIn is the actual time captured. DutyIn_Manual is to be shown on the Scheduler. Similarly, DutyOut_Manual goes into view.

Now, I need to split one row into two: One for Duty In and one for Duty Out.

Any help greatly appreciated.

Update
The solution by @Mikael Eriksson worked perfectly.

Here's my updated query:

DECLARE @T TABLE
(
    Id INT IDENTITY PRIMARY KEY,
    AttendanceId INT,
    StaffNumber VARCHAR(50),
    DutyDate DATE,
    DutyIn TIME,
    DutyOut TIME,
    DutyInManual TIME,
    DutyOutManual TIME
);

INSERT INTO @T
SELECT [iSAID],
    [cStaffNo],
    [dtDutyDate],
    [dtDutyIN],
    [dtDutyOUT],
    [dtDutyIN_Manual],
    [dtDutyOUT_Manual]
FROM [Attendance].[dbo].[StaffAttendance];

SELECT T.Id,
    T.AttendanceId,
    T.StaffNumber,
    T.DutyDate,
    S.DutyAuto,
    S.DutyManual,
    S.DutyType
FROM @T AS T
    CROSS APPLY
    (
        VALUES
            (T  .DutyIn, T.DutyInManual, 'In'),
            (T  .DutyOut, T.DutyOutManual, 'Out')
    ) AS S (DutyAuto, DutyManual, DutyType);

Best Answer

You can use the Table Value Constructor in a cross apply to generate two rows for each row in the source table.

declare  @T table
(
  ID int identity primary key,
  DutyIn datetime,
  DutyOut datetime,
  DutyInManual datetime,
  DutyOutManual datetime
);


insert into @T values 
('2016-10-01T10:10:00', '2016-10-01T10:50:00', '2016-10-01T10:00:00', '2016-10-01T11:00:00');

select T.ID,
       S.DutyAuto,
       S.DutyManual
from @T as T
  cross apply (values(T.DutyIn, T.DutyInManual),
                     (T.DutyOut, T.DutyOutManual)) as S(DutyAuto, DutyManual);
Related Question