代码之家 › 专栏 › 技术社区 › Kirk It looks like

基于PIVOT的用户可选择时间表和选择表设计

pivot sql-server-2008 sql

Kirk It looks like · 技术社区 · 12 年前

我正在创建一个日程安排应用程序,该应用程序允许员工输入存储在表中的所需日程安排。下面是使用SQL 2008 R2进行的当前设计。

CREATE TABLE [dbo].[Schedule] (
    [EmpNum] [varchar](10) NOT NULL,
    [Start] [datetime] NOT NULL,
    [Length] [decimal](18, 2) NOT NULL,
    [Reason] [varchar](1) NOT NULL,

    CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED 
    (
        [EmpNum] ASC,
        [Start] ASC
    )
)

需要注意的几点

用户可能没有输入特定日期范围的计划,但仍希望看到列出的姓名,但没有计划
用户可以不为一周中的某一天选择任何内容,但为其他日子选择某些内容
开始是轮班的开始
长是一个轮班的小时数,可能会因天而异,也可能因人而异
这个原因列表示用户已选择的内容(W-Work、P-PTO等)

这是示例数据

EmpNum     Start                   Length                                  Reason
---------- ----------------------- --------------------------------------- ------
000001     2012-08-02 09:00:00.000 12.00                                   W
000001     2012-08-04 08:00:00.000 9.50                                    P
000002     2012-08-02 08:30:00.000 10.00                                   W
000002     2012-08-03 19:00:00.000 12.00                                   W
000003     2012-08-03 08:00:00.000 8.00                                    P

我想要的输出是这样的

EmpNum     [0]    [1]    [2]    [3]    [4]    [5]    [6]
---------- ------ ------ ------ ------ ------ ------ ------
000001     NULL   NULL   NULL   NULL   W      NULL   P
000002     NULL   NULL   NULL   NULL   W      W      NULL
000003     NULL   NULL   NULL   NULL   NULL   P      NULL

我从来没有用过 PIVOT 之前的查询,因为我们刚刚从SQL 2000升级,所以请耐心等待。我构建了下面的查询,但由于以下错误而失败,我陷入了困境。

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '('.

查询

declare @FirstDayOfWeek date
set @FirstDayOfWeek = '7/29/2012'

select EmpNum,
@FirstDayOfWeek [0],
dateadd(day, 1, @FirstDayOfWeek) [1],
dateadd(day, 2, @FirstDayOfWeek) [2],
dateadd(day, 3, @FirstDayOfWeek) [3],
dateadd(day, 4, @FirstDayOfWeek) [4],
dateadd(day, 5, @FirstDayOfWeek) [5],
dateadd(day, 6, @FirstDayOfWeek) [6]
from Schedule
pivot (
    max(Reason)
    for Start in ([0], [1], [2], [3], [4], [5], [6])
) as Pvt

关于如何最好地实现这一点,或者我在这里错得有多严重,有什么想法吗?

1 回复 | 直到 12 年前

Taryn Frank Pearson 12 年前

看起来你正试图根据第1-7周的哪一天来调整你的数据。我建议对此稍作修改,以使其发挥作用:

SELECT *
FROM
(
    select EmpNum,
        reason,
        datepart(dw, start) as DyOfWk
    from #Schedule
) s
pivot (
    max(Reason)
    for dyofwk in ([1], [2], [3], [4], [5], [6], [7])
) as Pvt

看见 SQL Fiddle with Demo

结果:

enter image description here