sql - 如何在 SQL Server 列中显示行值？

Question

SQL FIDDLE DEMO HERE

我有 SheduleWorkers 表的表结构:

    CREATE TABLE SheduleWorkers
    (
         [Name] varchar(250),
         [IdWorker] varchar(250),    
         [IdDepartment] int,
         [IdDay] int,
         [Day] varchar(250)
    );


INSERT INTO SheduleWorkers ([Name],  [IdWorker],  [IdDepartment], [IdDay], [Day])
values 
('Sam', '001', 5, 1, 'Monday'),
('Lucas', '002', 5, 2, 'Tuesday'),
('Maria', '003', 5, 1, 'Monday'),
('José', '004', 5, 3, 'Wednesday'),
('Julianne', '005', 5, 3, 'Wednesday'),
('Elisa', '006', 18, 1, 'Monday'),
('Gabriel', '007', 23, 5, 'Friday');

我需要在每个工作日显示当天工作的部门 5 的 worker 姓名，如下所示:

MONDAY   TUESDAY   WEDNESDAY  THURSDAY  FRIDAY  SATURDAY
------   -------   ---------  --------  ------  -------
Sam       Lucas    Jose       
Maria              Julianne

我怎样才能得到这个结果，我接受建议，谢谢。

Answer 1

DECLARE @SheduleWorkers TABLE
    (
      [Name] VARCHAR(250) ,
      [IdWorker] VARCHAR(250) ,
      [IdDepartment] INT ,
      [IdDay] INT ,
      [Day] VARCHAR(250)
    );


INSERT  INTO @SheduleWorkers
        ( [Name], [IdWorker], [IdDepartment], [IdDay], [Day] )
VALUES  ( 'Sam', '001', 5, 1, 'Monday' ),
        ( 'Lucas', '002', 5, 2, 'Tuesday' ),
        ( 'Maria', '003', 5, 1, 'Monday' ),
        ( 'José', '004', 5, 3, 'Wednesday' ),
        ( 'Julianne', '005', 5, 3, 'Wednesday' ),
        ( 'Elisa', '006', 18, 1, 'Monday' ),
        ( 'Gabriel', '007', 23, 5, 'Friday' );

;
WITH    cte
          AS ( SELECT   Name ,
                        Day ,
                        ROW_NUMBER() OVER ( PARTITION BY Day ORDER BY [IdWorker] ) AS rn
               FROM     @SheduleWorkers
             )
    SELECT  [MONDAY] ,
            [TUESDAY] ,
            [WEDNESDAY] ,
            [THURSDAY] ,
            [FRIDAY] ,
            [SATURDAY]
    FROM    cte PIVOT( MAX(Name) FOR day IN ( [MONDAY], [TUESDAY], [WEDNESDAY],
                                              [THURSDAY], [FRIDAY], [SATURDAY] ) ) p

输出:

MONDAY  TUESDAY WEDNESDAY   THURSDAY    FRIDAY  SATURDAY
Sam     Lucas   José        NULL        Gabriel NULL
Maria   NULL    Julianne    NULL        NULL    NULL
Elisa   NULL    NULL        NULL        NULL    NULL

主要思想是公共(public)表表达式中的row_number窗口函数，它将为您提供与一天内最大重复项相同的行数。