SQL 动态透视 "Week"表

餐 table 设计:

| PeriodStart | Person | Day1 | Day2 | Day3 | Day4 | Day5 | Day6 | Day7 |
-------------------------------------------------------------------------
| 01/01/2018  |  123   |  2   |  4   |  6   |  8   |  10  |  12  |  14  |
| 01/15/2018  |  246   |  1   |  3   |  5   |  7   |  9   |  11  |  13  |

我正在尝试创建一个可以动态转置两行的数据透视语句。

期望的输出:

|    Date    | Person | Values |
--------------------------------
| 01/01/2018 |   123  |    2   |
| 01/02/2018 |   123  |    4   |
| 01/03/2018 |   123  |    6   |
| 01/04/2018 |   123  |    8   |
| 01/05/2018 |   123  |   10   |
| 01/06/2018 |   123  |   12   |
| 01/15/2018 |   246  |    1   |
| 01/16/2018 |   246  |    3   |
| 01/17/2018 |   246  |    5   |
 ... and so on. Date order not important

以下查询将有助于初始化事物:

DECLARE @WeekTable TABLE (
    [PeriodStart] datetime
    , [Person] int
    , [Day1] int
    , [Day2] int
    , [Day3] int
    , [Day4] int
    , [Day5] int
    , [Day6] int
    , [Day7] int
)

INSERT INTO @WeekTable(
    [PeriodStart],[Person],[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7]
)
VALUES ('01/01/2018','123','2','4','6','8','10','12','14')
    ,('01/15/2018','246','1','3','5','7','9','11','13')

最佳答案

其他选择是使用 APPLY 运算符来做到这一点

SELECT DATEADD(DAY, Days-1, Dates) Date, a.Person, Value
FROM @WeekTable t CROSS APPLY (
    VALUES (PeriodStart, Person, 1, Day1), (PeriodStart, Person, 2, Day2),
           ..., (PeriodStart, Person, 7, Day7)
)a(Dates, Person, Days, Value)

关于SQL 动态透视 "Week"表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49163768/