将4行(1列)转换为5列(1行)时出现透视问题

Row_Number() 只返回列表1-5中的一个数字,这样它就不会与列表“day1,day2,day3,day4,day5”中的任何内容匹配。我在前面加了“day”,所以它会。

另外你还有 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

下面是另一个不涉及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