sql - 如何将行中的一些值转置为列？

Question

我有一张存储人们轮班信息的表格。它的结构是：

SHIFT_TABLE
-----------
ShiftID  PeopleID  ShiftDate   ShiftStart         ShiftEnd
1        41        2020-08-24  2020-08-24 06:00   2020-08-24 14:00
2        41        2020-08-26  2020-08-26 06:00   2020-08-26 14:00
3        41        2020-08-27  2020-08-27 19:00   2020-08-28 03:00
4        41        2020-08-29  2020-08-29 10:00   2020-08-29 16:00
5        58        2020-08-24  2020-08-24 14:00   2020-08-24 21:30
6        58        2020-08-25  2020-08-25 14:00   2020-08-25 23:00
7        58        2020-08-30  2020-08-30 08:00   2020-08-30 18:00

Columns     Data types
-------     ----------
ShiftDate   date
ShiftStart  datetime
ShiftEnd    datetime

用户只发送一个日期（没有时间），新的存储过程将返回该周（从周日到周六）所有人的班次信息。因此，如果使用从一周中选择任何日期，例如星期一或星期三，结果将完全相同。

例如，如果用户发送2020-08-25到 SP，将返回除 ShiftID 为 7 的行之外的所有行。

发送2020-08-25时，结果应如下所示：

Person  2020-08-23  2020-08-24   2020-08-25   2020-08-26   2020-08-27   2020-08-28  2020-08-29
41      OFF         06:00-14:00  OFF          06:00-14:00  19:00-03:00  OFF         10:00-16:00
58      OFF         14:00-21:30  14:00-21:30  OFF          OFF          OFF         OFF

如果使用日期名称而不是日期作为列名，我很好，因为这种方法可以避免动态 SQL：

Person  Sunday  Monday       Tuesday      Wednesday    Thursday     Friday  Saturday
41      OFF     06:00-14:00  OFF          06:00-14:00  19:00-03:00  OFF     10:00-16:00
58      OFF     14:00-21:30  14:00-21:30  OFF          OFF          OFF     OFF

我想避免使用动态 SQL 或 XML解决方案。我可以获得工作日编号（1 代表星期日，2 代表星期一，等等）。我不确定这是否会有所帮助。有没有办法只ShiftStart用. 我已经可以过滤结果集了。列名每周都会更改。我想不惜一切代价避免动态 SQL 的一些建议。ShiftEndPoepleIDShiftDate

使用 pivot 可能有一些方法可以做到这一点，但它似乎是一种较慢的方法。

标签： sqlsql-serverssmssql-server-2016transpose

Answer 1

解释

如果您不想使用动态 sql 并且可以将“星期一，星期二...星期日”（或每天的等效数字 1=星期一，2=星期二...）作为列标题而不是实际日期像“2020.08.23 2020.08.24 2020.08.25 2020.08.26 2020.08.27 2020.08.28 2020.08.29”这样的值，那么您的要求是可以实现的：

查询

declare @WorkDate date = '2020.08.25', @StartFilter date, @EndFilter date

declare @shift_table table ( ShiftID int identity(1,1), PeopleID int, ShiftDate varchar(max), ShiftStart datetime, ShiftEnd datetime )
insert into @shift_table (PeopleID, ShiftDate, ShiftStart, ShiftEnd )
      select 41,        '2020.08.24',  '2020-08-24 06:00',   '2020-08-24 14:00'
union select 41,        '2020.08.26',  '2020-08-26 06:00',   '2020-08-26 14:00'
union select 41,        '2020.08.27',  '2020-08-27 19:00',   '2020-08-28 03:00'
union select 41,        '2020.08.29',  '2020-08-29 10:00',   '2020-08-29 16:00'
union select 58,        '2020.08.24',  '2020-08-24 14:00',   '2020-08-24 21:30'
union select 58,        '2020.08.25',  '2020-08-25 14:00',   '2020-08-25 23:00'
union select 58,        '2020.08.30',  '2020-08-30 08:00',   '2020-08-30 18:00'

-- get the week start and end date filters based on the @WorkDate passed in
SELECT @StartFilter =  DATEADD(dd, -(DATEPART(dw, @WorkDate)-1), @WorkDate) /* week start */,
       @EndFilter = DATEADD(dd, 7-(DATEPART(dw, @WorkDate)), @WorkDate) /* week end */

select
    Person,
    min(Monday) as [Monday],
    min(Tuesday) as [Tuesday],
    min(Wednesday) as [Wednesday], 
    min(Thursday) as [Thursday], 
    min(Friday) as [Friday], 
    min(Saturday) as [Saturday], 
    min(Sunday) as [Sunday]
from (
    select
        PeopleID as Person,
        isnull([Monday],'OFF') as [Monday],
        isnull([Tuesday],'OFF') as [Tuesday],
        isnull([Wednesday],'OFF') as [Wednesday],
        isnull([Thursday],'OFF') as [Thursday],
        isnull([Friday],'OFF') as [Friday],
        isnull([Saturday],'OFF') as [Saturday],
        isnull([Sunday],'OFF') as [Sunday]
    from (
    select
        *,
        DATENAME(dw,cast(ShiftDate as date)) [Day],
        format(ShiftStart,'HHmm','en-us') + '-' + format(ShiftEnd,'HHmm','en-us') ShiftTime
    from @shift_table
    where cast(ShiftDate as date) between @StartFilter and @EndFilter
    ) src
    pivot
    (
        max(ShiftTime)
        for [Day] in ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])
    ) piv
) P
group by P.Person

结果