如何在SQL Server中将行值显示为列？

Question

这里是 SQL FIDDLE DEMO

我有一个 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在该日工作的工人的名称，就像这样：

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

我想知道如何得到这个结果，欢迎提供建议，谢谢。

- Esraa_92

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

主要思想是使用通用表达式中的row_number窗口函数，在一天内最大重复次数相同时将提供与之相同数量的行。

- Giorgi Nakeuri

---

感谢您的答案，您的代码对我来说正常工作 ;-D - Esraa_92

Answer 2

您可以使用数据透视表来解决此问题。请使用以下查询语句，并使用分区功能。

SELECT  [Monday] , [Tuesday] , [Wednesday] , [Thursday] , [Friday], [SATURDAY]
FROM 
(SELECT [Day],[Name],RANK() OVER (PARTITION BY [Day] ORDER BY [Day],[Name]) as rnk
 FROM SheduleWorkers) p
 PIVOT(
Min([Name])
FOR [Day] IN
( [Monday] , [Tuesday] , [Wednesday] , [Thursday] , [Friday], [SATURDAY]  )
) AS pvt

- Sandeep Kumar

---

非常感谢您的回答，;-D - Esraa_92

不客气 :) @Esraa_92 - Sandeep Kumar

@Esraa_92，我不介意，但你能解释一下为什么你接受了比我的回答晚10分钟的答案吗？只是想知道我做错了什么 :) - Giorgi Nakeuri

@Giorgi Nakeuri，谢谢你的帮助，你的代码完全正确，我接受这个答案，因为我不习惯使用递归语句，因为我不掌握CTE语句的命令，就是这样。这段代码对我来说更容易理解。 - Esraa_92

Answer 3

我建议您使用动态SQL和PIVOT来获取所需的所有日期名称：

DECLARE @column nvarchar(max),
        @sql nvarchar(max)

;WITH cte AS (
SELECT DATENAME(WEEKDAY,0) as [Day], 1 as [Level]
UNION ALL
SELECT DATENAME(WEEKDAY,[Level]), [Level] + 1
FROM cte
WHERE [Level] < 7
)

SELECT @column = STUFF((SELECT ','+QUOTENAME([Day]) FROM cte ORDER BY [Level]FOR XML PATH('')),1,1,'')

SELECT @sql = 
'SELECT '+@column+'
FROM (
    SELECT Name, [Day], RANK() OVER (PARTITION BY [Day] ORDER BY [Day],IdWorker) as rn
    FROM #SheduleWorkers
) as p
PIVOT
(
MAX(NAMe) FOR [Day] IN ('+@column+')
) as pvt'

EXEC(@sql)

输出：

Monday  Tuesday Wednesday   Thursday    Friday  Saturday    Sunday
Sam     Lucas   Jose        NULL        Gabriel NULL        NULL
Maria   NULL    Julianne    NULL        NULL    NULL        NULL
Elisa   NULL    NULL        NULL        NULL    NULL        NULL

- gofr1