将 4 行（1 列）转换为 5 列（1 行）时的 PIVOT 问题

Question

我有一个表 var，其中包含一些行，但只有一列 DATETIME 类型，如下所示：

[Day]
2010-08-03
2010-08-04
2010-08-10
2010-08-11

我需要在某些列上显示，但仅在一行中显示。 我的结果集将限制为 5 行，那么我也可以限制为 5 列。 我需要的示例：

[Day1]      [Day2]      [Day3]      [Day4]      [Day5]
2010-08-03  2010-08-04  2010-08-10  2010-08-11  NULL

我尝试在 SQL Server 2005 中使用 PIVOT 来完成此操作。 但所有示例都使用更多列来聚合值，那么我不明白。

这是我正在尝试的查询：

SELECT r.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY Day ASC) Line, Day FROM @MyDays) AS o
PIVOT (MIN(Line) FOR Day IN (Day1, Day2, Day3, Day4, Day5)) AS r

但结果都是 NULL：

[Day1]  [Day2]  [Day3]  [Day4]  [Day5]
NULL    NULL    NULL    NULL    NULL

有人可以告诉我我做错了什么吗？

Answer 1

Row_Number() 仅返回列表 1-5 中的一个数字，因此它不会与列表“Day1、Day2、Day3、Day4、Day5”中的任何内容匹配。我已经在前面附加了“日”，这样就可以了。

另外，您还有 MIN(Line) FOR Day IN，这需要反过来。这是您要显示的Day 的值。

;with mydays as
(

SELECT '2010-08-03' AS [Day] UNION
SELECT '2010-08-04' AS [Day] UNION
SELECT '2010-08-10' AS [Day] UNION
SELECT '2010-08-11' AS [Day] 
)
SELECT r.* FROM (
   SELECT 
      'Day' + CAST( ROW_NUMBER() OVER (ORDER BY Day ASC)as varchar(10)) Line, 
       Day 
   FROM mydays) AS o
PIVOT (MIN([Day]) FOR Line IN (Day1, Day2, Day3, Day4, Day5)) AS r

Answer 2

这是另一个不涉及 Pivot 语句的解决方案：

With RawData As
    (
    Select '2010-08-03' AS [Day]
    Union All Select '2010-08-04' 
    Union All Select '2010-08-10'
    Union All Select '2010-08-11'
    )
    , NumberedItems As
    (
    Select Day, Row_Number() Over( Order By Day ) As Line
    From RawData
    )
Select Min ( Case When Line = 1 Then [Day] End ) As Day1
    , Min ( Case When Line = 2 Then [Day] End ) As Day2
    , Min ( Case When Line = 3 Then [Day] End ) As Day3
    , Min ( Case When Line = 4 Then [Day] End ) As Day4
    , Min ( Case When Line = 5 Then [Day] End ) As Day5
From NumberedItems