Print

Split a row into multiple rows based on a column value

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:
copyraw 
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
  1.  SELECT 
  2.          EmployeeNo 
  3.          , DaysOffSick 
  4.          , DateOfSickness 
  5.  FROM    Employees_Attendance_Table 
  6.   
  7.   
  8.  -- yields 
  9.   
  10.          EmployeeNo      DaysOffSick     DateOfSickness 
  11.          --------------  --------------  ---------------- 
  12.          001             1.00            1997-11-17 
  13.          002             3.00            2000-02-18 
  14.          003             2.00            1999-02-25 
What we want:
copyraw 
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)
  1.  EmployeeNo      DaysOffSick     DateOfSickness 
  2.  --------------  --------------  ---------------- 
  3.  001             1.00            1997-11-17 
  4.  002             1.00            2000-02-18 
  5.  002             1.00            2000-02-19 
  6.  002             1.00            2000-02-20 
  7.  003             1.00            1999-02-25 
  8.  003             1.00            1999-02-26 
  9.   
  10.  -- note the dates increment and do not account for days off (eg. Saturday / Sunday) 

How?
Adapted from a forum topic: SQLTeam Forums
First of all, we want the rows to multiply based on the value of the column:
copyraw 
SELECT
        EmployeeNo
        , DaysOffSick
        , DateOfSickness
        ,'1' + substring(CAST(DaysOffSick AS VARCHAR(10)), 2, 1000) AS Items
FROM
        Employees_Attendance_Table
JOIN
        master..spt_values n
        ON n.type = 'P'
        AND n.number < CONVERT(int, LEFT(CAST( DaysOffSick AS INT ),3))


-- yields
        EmployeeNo      DaysOffSick     DateOfSickness    Items
        --------------  --------------  ----------------  --------
        001             1.00            1997-11-17        1.00
        002             3.00            2000-02-18        1.00
        002             3.00            2000-02-18        1.00
        002             3.00            2000-02-18        1.00
        003             2.00            1999-02-25        1.00
        003             2.00            1999-02-25        1.00
  1.  SELECT 
  2.          EmployeeNo 
  3.          , DaysOffSick 
  4.          , DateOfSickness 
  5.          ,'1' + substring(CAST(DaysOffSick AS VARCHAR(10)), 2, 1000) AS Items 
  6.  FROM 
  7.          Employees_Attendance_Table 
  8.  JOIN 
  9.          master..spt_values n 
  10.          ON n.type = 'P' 
  11.          AND n.number < CONVERT(int, LEFT(CAST( DaysOffSick AS INT ),3)) 
  12.   
  13.   
  14.  -- yields 
  15.          EmployeeNo      DaysOffSick     DateOfSickness    Items 
  16.          --------------  --------------  ----------------  -------- 
  17.          001             1.00            1997-11-17        1.00 
  18.          002             3.00            2000-02-18        1.00 
  19.          002             3.00            2000-02-18        1.00 
  20.          002             3.00            2000-02-18        1.00 
  21.          003             2.00            1999-02-25        1.00 
  22.          003             2.00            1999-02-25        1.00 
If you have more than 999 items in the column, then you may need to increment the 3 in the above statement "LEFT(..., 3)".

Almost there, but in the above example, the dates are wrong for subsequent days off sick:
copyraw 
SELECT
        EmployeeNo
        , DaysOffSick
        , DateOfSickness
        ,'1' + substring(CAST(DaysOffSick AS VARCHAR(10)), 2, 1000) AS Items
        , ROW_NUMBER() OVER (PARTITION BY EmployeeNo, DateOfSickness ORDER BY EmployeeNo, DateOfSickness) AS Rownum
FROM
        Employees_Attendance_Table
JOIN
        master..spt_values n
        ON n.type = 'P'
        AND n.number < CONVERT(int, LEFT(CAST( DaysOffSick AS INT ),3))


-- yields
        EmployeeNo      DaysOffSick     DateOfSickness    Items        Rownum
        --------------  --------------  ----------------  -----------  -----------------
        001             1.00            1997-11-17        1.00         1
        002             3.00            2000-02-18        1.00         1
        002             3.00            2000-02-18        1.00         2
        002             3.00            2000-02-18        1.00         3
        003             2.00            1999-02-25        1.00         1
        003             2.00            1999-02-25        1.00         2
  1.  SELECT 
  2.          EmployeeNo 
  3.          , DaysOffSick 
  4.          , DateOfSickness 
  5.          ,'1' + substring(CAST(DaysOffSick AS VARCHAR(10)), 2, 1000) AS Items 
  6.          , ROW_NUMBER() OVER (PARTITION BY EmployeeNo, DateOfSickness ORDER BY EmployeeNo, DateOfSickness) AS Rownum 
  7.  FROM 
  8.          Employees_Attendance_Table 
  9.  JOIN 
  10.          master..spt_values n 
  11.          ON n.type = 'P' 
  12.          AND n.number < CONVERT(int, LEFT(CAST( DaysOffSick AS INT ),3)) 
  13.   
  14.   
  15.  -- yields 
  16.          EmployeeNo      DaysOffSick     DateOfSickness    Items        Rownum 
  17.          --------------  --------------  ----------------  -----------  ----------------- 
  18.          001             1.00            1997-11-17        1.00         1 
  19.          002             3.00            2000-02-18        1.00         1 
  20.          002             3.00            2000-02-18        1.00         2 
  21.          002             3.00            2000-02-18        1.00         3 
  22.          003             2.00            1999-02-25        1.00         1 
  23.          003             2.00            1999-02-25        1.00         2 

Great! Now let's just add the Rownum value to the date of sickness (minus 1 for obvious reasons - 25th + row(1) = 26th when the person was off the 25th):
copyraw 
SELECT
        EmployeeNo
        , '1' + substring(CAST(DaysOffSick AS VARCHAR(10)), 2, 1000) AS DaysOffSick
        , DATEADD(d, ROW_NUMBER() OVER (PARTITION BY EmployeeNo, DateOfSickness ORDER BY EmployeeNo, DateOfSickness) - 1, DateOfSickness)  AS DateOffSick
FROM
        Employees_Attendance_Table
JOIN
        master..spt_values n
        ON n.type = 'P'
        AND n.number < CONVERT(int, LEFT(CAST( DaysOffSick AS INT ),3))


-- yields
        EmployeeNo      DaysOffSick     DateOffSick
        --------------  --------------  ---------------------
        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
  1.  SELECT 
  2.          EmployeeNo 
  3.          , '1' + substring(CAST(DaysOffSick AS VARCHAR(10)), 2, 1000) AS DaysOffSick 
  4.          , DATEADD(d, ROW_NUMBER() OVER (PARTITION BY EmployeeNo, DateOfSickness ORDER BY EmployeeNo, DateOfSickness) - 1, DateOfSickness)  AS DateOffSick 
  5.  FROM 
  6.          Employees_Attendance_Table 
  7.  JOIN 
  8.          master..spt_values n 
  9.          ON n.type = 'P' 
  10.          AND n.number < CONVERT(int, LEFT(CAST( DaysOffSick AS INT ),3)) 
  11.   
  12.   
  13.  -- yields 
  14.          EmployeeNo      DaysOffSick     DateOffSick 
  15.          --------------  --------------  --------------------- 
  16.          001             1.00            1997-11-17 
  17.          002             1.00            2000-02-18 
  18.          002             1.00            2000-02-19 
  19.          002             1.00            2000-02-20 
  20.          003             1.00            1999-02-25 
  21.          003             1.00            1999-02-26 

Some thing(s) to consider:
  1. User running the SQL query needs permission to read the master database.
  2. If run by a service account, ask a DBA to emulate the user for testing.
  3. the Items column may vary from 1 and 10+. Not sure on why this is but it doesn't seem to affect the functionality of the above and you could always hardcode a 1.00 if its confusing.
  4. In theory, if you know the shift patterns of an employee, you could enhance the above to skip day-off dates (eg. Saturday/Sunday - the above does not take this into account - my day-job company is a 24/7 business).
Category: Transact-SQL :: Article: 519