Split a row into multiple rows based on a column value

Applies to
  • Transact-SQL (T-SQL)
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

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.