CODEWITHSUNDEEP
sql-serverdaterow
Kristin Vernon
Kristin Vernon

Reputation: 155

SQL Query that splits a row into multiple rows based on the dates of Monday to Sunday of a particular week

So basically as the title says, may sound confusing, but this is how my table is set up:

+-----------+--------------------------+---------+-----------+------------+-----------------+-------------+---------+
| RecordID  | WeekCommencing |  Name    | Monday  | Tuesday  | Wednesday  | Thursday  | Friday  | Saturday  | Sunday | 

+-----------+--------------------------+---------+-----------+------------+-----------------+-------------+---------+

|         1 | 2020-08-10     | John Doe | WH      | WH       | RW         | WH        | WH      | DO        | DO     |
+-----------+----------------+----------+---------+----------+------------+-----------+---------+-----------+--------+

What I want to do is query the records in the table and separate each day into it's respective date, "WeekCommencing" always a Monday so using the above table, I want to manipulate it to look like this:

+-----------+--------------------------+---------+-----------+-----+
| RecordID  | WeekCommencing |  Name    | Date       | Category  |

+-----------+--------------------------+---------+-----------+------+

|         1 | 2020-08-10     | John Doe | 2020-08-10  | WH      |
+-----------+----------------+----------+---------+----------+------+

|         1 | 2020-08-10     | John Doe | 2020-08-11  | WH      |
+-----------+----------------+----------+---------+----------+------+

|         1 | 2020-08-10     | John Doe | 2020-08-12  | RW      |
+-----------+----------------+----------+---------+----------+------+

|         1 | 2020-08-10     | John Doe | 2020-08-13  | WH      |
+-----------+----------------+----------+---------+----------+------+

|         1 | 2020-08-10     | John Doe | 2020-08-14  | WH      |
+-----------+----------------+----------+---------+----------+------+

|         1 | 2020-08-10     | John Doe | 2020-08-15  | DO      |
+-----------+----------------+----------+---------+----------+------+

|         1 | 2020-08-10     | John Doe | 2020-08-16  | DO      |
+-----------+----------------+----------+---------+----------+------+

So as you can see, the week commencing marks the beginning of the week which is a Monday, therefore the date will be Monday and the category associated with Monday is assigned to that date, then Tuesday the 11th and the category assigned to Tuesday on that date, and so on until the following Monday then it starts again. How would I be able to accomplish this?

Upvotes: 0

Views: 84

Answers (1)

GarethD
GarethD

Reputation: 69789

You can unpivot your data using CROSS APPLY and a table value constructor, e.g.

DECLARE @DummyData TABLE
(
    RecordID INT,
    WeekCommencing DATE,
    Name VARCHAR(8),
    Monday VARCHAR(2),
    Tuesday VARCHAR(2),
    Wednesday VARCHAR(2),
    Thursday VARCHAR(2),
    Friday VARCHAR(2),
    Saturday VARCHAR(2),
    Sunday VARCHAR(2)
);
INSERT  @DummyData(RecordID, WeekCommencing, Name, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday)
VALUES
    (1, '20200810', 'John Doe', 'WH', 'WH', 'RW', 'WH', 'WH', 'DO', 'DO');

SELECT  t.RecordID,
        t.WeekCommencing,
        t.Name,
        Date = DATEADD(DAY, upvt.AddDays, t.WeekCommencing),
        upvt.Category
FROM    @DummyData AS t
        CROSS APPLY
        (VALUES 
            (0, t.Monday),
            (1, t.Tuesday),
            (2, t.Wednesday),
            (3, t.Thursday),
            (4, t.Friday),
            (5, t.Saturday),
            (6, t.Sunday)
        ) AS upvt (AddDays, Category);

OUTPUT

RecordID    WeekCommencing  Name        Date        Category
---------------------------------------------------------------
1           2020-08-10      John Doe    2020-08-10  WH
1           2020-08-10      John Doe    2020-08-11  WH
1           2020-08-10      John Doe    2020-08-12  RW
1           2020-08-10      John Doe    2020-08-13  WH
1           2020-08-10      John Doe    2020-08-14  WH
1           2020-08-10      John Doe    2020-08-15  DO
1           2020-08-10      John Doe    2020-08-16  DO

Upvotes: 1

Related Questions