从表中创建名册

Question

我有一个原始表，其中有这样格式的原始数据：

gkey    Start_date  Finish_date Start_Hr Finish_hr  Name
 1      2014-01-01  2014-10-16  07       15         Smith  
 2      2014-01-01  2014-12-31  15       21         Johnes
 3      2014-01-01  2014-12-31  21       07         Adams
 4      2014-10-16  2014-12-31  21       07         Doe
 ...

我想以每周名册的形式展示

Shift      today      today+1     today+2      today+3....
Morning    Smith      Smith       Doe
Afternoon  Johnes     Johnes      Johnes
Evening   .....

我一开始就把它放到漂亮的虚拟桌子上

declare @d datetime
declare @c integer
declare @T table (
[day] datetime,
name varchar(max),
shift varchar(max)
)


 set @c=1
 set @d= getdate();


 while @c<=7
 begin
 insert into @T
 select @d as [day],
 name,
 case when finish_hr <=6 then 'Morning' when finish_hr<=15 then 'Afternoon' when     finish_hr<=23 then 'Evening' end as shift
from calling_roster where start_date <=@d and finish_date >@d


 set @c= @c+1
 set @d = dateadd(day,1,@d)
 end

所以现在我把它很好地设置为

day      name    shift
14-10    Smith   Morning
14-10    Johnes  Afternoon
14-10    Adams   Evening
15-10    Smith   Morning

现在我被困在..。

Answer 1

这可以通过枢轴查询来完成。

在这里，我把你的约会划分为一周中的几天：

SELECT [name], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday]
FROM (
    SELECT [name], DATENAME(dw, [day]) AS DayWeek, [shift]
    FROM table1
    ) AS src
    pivot (
        max([shift]) FOR DayWeek IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])
    ) AS pvt

您可以看到它在这把小提琴中工作。