Applies to
What?
This is a quick article on how to split a single row into multipe rows based on the value of a column in the same table.
Why?
I have a table that has all the days of sickness of employees. This table contains, which employee, on what date, and for how many days they were off sick. When migrating to a new system, the destination wanted 1 row per day. This meant that if in the old system, there was 1 row with an employee who took 2 days off, we would want 2 rows for that date for the same employee in the new system.
What we have:
copyrawstyler
SELECT
EmployeeNo
, DaysOffSick
, DateOfSickness
FROM Employees_Attendance_Table
-- yields
EmployeeNo DaysOffSick DateOfSickness
-------------- -------------- ----------------
001 1.00 1997-11-17
002 3.00 2000-02-18
003 2.00 1999-02-25
- SELECT
- EmployeeNo
- , DaysOffSick
- , DateOfSickness
- FROM Employees_Attendance_Table
-
-
-
- EmployeeNo DaysOffSick DateOfSickness
- 001 1.00 1997-11-17
- 002 3.00 2000-02-18
- 003 2.00 1999-02-25
What we want:
copyrawstyler
EmployeeNo DaysOffSick DateOfSickness
-------------- -------------- ----------------
001 1.00 1997-11-17
002 1.00 2000-02-18
002 1.00 2000-02-19
002 1.00 2000-02-20
003 1.00 1999-02-25
003 1.00 1999-02-26
-- note the dates increment and do not account for days off (eg. Saturday / Sunday)
- EmployeeNo DaysOffSick DateOfSickness
- 001 1.00 1997-11-17
- 002 1.00 2000-02-18
- 002 1.00 2000-02-19
- 002 1.00 2000-02-20
- 003 1.00 1999-02-25
- 003 1.00 1999-02-26
-
How?Category: Transact-SQL :: Article: 519