SQL查询可根据特定一周的星期一至星期日将行分成多行

克里斯汀·弗农

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

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

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

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

我想做的就是查询表中的记录，并将每一天分别分隔为相应的日期，“ WeekCommencing”始终为星期一，因此使用上面的表，我要对其进行处理，使其看起来像这样：

+-----------+--------------------------+---------+-----------+-----+
| 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日的星期二，并将分配给该星期二的类别日期，依此类推，直到下一个星期一，然后又重新开始。我将如何做到这一点？

加雷斯

您可以使用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

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。