SQL查询，根据特定星期一到星期日的日期将一行拆分为多行

Question

因此，基本上正如标题所说，听起来可能令人困惑，但这就是我的表的设置方式：

+-----------+--------------------------+---------+-----------+------------+-----------------+-------------+---------+
| RecordID  | WeekCommencing |  Name    | Monday  | Tuesday  | Wednesday  | Thursday  | Friday  | Saturday  | Sunday | 
+-----------+--------------------------+---------+-----------+------------+-----------------+-------------+---------+
|         1 | 2020-08-10     | John Doe | WH      | WH       | RW         | WH        | WH      | DO        | DO     |
+-----------+----------------+----------+---------+----------+------------+-----------+---------+-----------+--------+

我想做的是查询表中的记录，并将每一天划分为相应的日期，"；周开始"；总是一个星期一，所以使用上表，我想把它操作成这样：

+-----------+--------------------------+---------+-----------+-----+
| 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      |
+-----------+----------------+----------+---------+----------+------+

正如你所看到的，开始的一周标志着一周的开始，这是一个星期一，因此日期将是星期一，与星期一相关的类别被分配给该日期，然后是11日星期二，类别被分配到该日期的星期二，以此类推，直到下一个星期，然后它再次开始。我怎样才能做到这一点？

Answer 1

您可以使用CROSS APPLY和表值构造函数(例如(来取消数据透视

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);

输出

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