Transform rows in columns by Day Name

Question

I have this table

|ID | day_name   |        day_date     |
  1   Monday     2018-01-08 00:00:00.000
  2   Monday     2018-01-15 00:00:00.000
  3   Monday     2018-01-22 00:00:00.000
  4   Monday     2018-01-29 00:00:00.000
 10   Tuesday    2018-01-16 00:00:00.000
 11   Tuesday    2018-01-23 00:00:00.000
 12   Tuesday    2018-01-30 00:00:00.000

I want to rotate the rows to columns so the result set is like this:

|        Monday            |       Tuesday           |
2018-01-08 00:00:00.000     2018-01-16 00:00:00.000
2018-01-15 00:00:00.000     2018-01-23 00:00:00.000
2018-01-22 00:00:00.000     2018-01-30 00:00:00.000
2018-01-29 00:00:00.000

I've tried something but the results are not as expected:

WITH dayz AS
(
    SELECT day_name, day_date,
    [rn] = RANK() OVER (PARTITION BY day_name ORDER BY day_date)
    FROM ISP_Cloud_DaysFromSession
)
SELECT 
    day_name,
     MondayDates = MAX(CASE WHEN rn = 1 THEN day_date ELSE NULL END),
  TuesdayyDates = MAX(CASE WHEN rn = 2 THEN day_date ELSE NULL END),
  Column3 = MAX(CASE WHEN rn = 3 THEN day_date ELSE NULL END),
  Column4 = MAX(CASE WHEN rn = 4 THEN day_date ELSE NULL END),
  Column5 = MAX(CASE WHEN rn = 5 THEN day_date ELSE NULL END),
  Column6 = MAX(CASE WHEN rn = 6 THEN day_date ELSE NULL END),
  Column7 = MAX(CASE WHEN rn = 7 THEN day_date ELSE NULL END),
  Column8 = MAX(CASE WHEN rn = 8 THEN day_date ELSE NULL END),
  Column9 = MAX(CASE WHEN rn = 9 THEN day_date ELSE NULL END)
  FROM
  dayz
  GROUP BY day_name

Any ideas how to transform this?

Answer 1

You weren't far off, but you're grouping by the wrong thing. The Row Number is what you want in each row, but you've defined it as your columns

WITH Days AS
    (SELECT day_name,
            day_date,
            RANK() OVER (PARTITION BY day_name ORDER BY day_date) AS [rn]
     FROM dbo.ISP_Cloud_DaysFromSession)
SELECT MAX(CASE day_name WHEN 'Monday' THEN day_date END) AS Monday,
       MAX(CASE day_name WHEN 'Tuesday' THEN day_date END) AS Tuesday
FROM Days
GROUP BY rn
ORDER BY rn ASC;

Answer 2

You can also use row_number and pivot:

WITH Days AS (
  SELECT ROW_NUMBER() OVER (PARTITION BY day_name ORDER BY day_date) rr
    , day_name, day_date
  FROM ISP_Cloud_DaysFromSession
)
SELECT [Monday], [Tuesday]
FROM (
  SELECT *
  FROM Days
) AS SOURCE
PIVOT (
  MAX (day_date)
  FOR day_name IN ([Monday], [Tuesday])
) AS pivotable