PIVOT problem when converting 4 Rows (1 Column) to 5 Columns (1 Row)

Question

I have a table var with some rows but only one column of type DATETIME, like this:

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

I need to show on some columns but in only one row. My result set will be limited to 5 rows, then I can limit to 5 columns too. Example of what I need:

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

I was trying to do it using PIVOT in SQL Server 2005. But all examples uses more columns to agregate values, then I'm not understanding.

This is the query I was trying:

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

But the result is all NULL:

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

Could someone show me what I'm doing wrong?

Answer 1

Row_Number() just returns a number in the list 1-5 so it isn't going to match anything in your list "Day1, Day2, Day3, Day4, Day5". I have appended "Day" onto the front so it will.

Additionally you have MIN(Line) FOR Day IN, This needs to be the otherway around. It is the value of Day that you are wanting to display.

;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

Here's another solution which does not involve the Pivot statement:

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