Convert date and week of day to calendar format using pivot

Question

I have the following data in the temp table

I want it to convert into the calendar format using pivot as

but due to aggregate it only shows 1 row

 SELECT
     *
 FROM
     (SELECT
          CONVERT(VARCHAR(10), dt, 106) AS Date1,
          dw AS Wd
      FROM
          #tbl) t 
 PIVOT
     (MAX(Date1)
         FOR Wd IN ([Sunday], [Monday], [Tuesday], [Wednesday],[Thursday], [Friday], [Saturday]) 
     ) AS pivotTable

as per the comments, I have following update

  With CTE (dt,dw,last)
as
(
select Cast(dateadd(day, -day(GetDate())+1,GetDate())as date),datename(dw,Cast(dateadd(day, -day(GetDate())+1,GetDate())as date)),
cast(dateadd(day,-1,dateadd(Month,1,Cast(dateadd(day, -day(GetDate())+1,GetDate())as date))) as date)
union all
select cast(dateadd(day,1,dt) as date), DATENAME(DW,cast(dateadd(day,1,dt) as date)),last
from CTE
where cast(dateadd(day,1,dt) as date)<last
)
select * from (
select dt,dw,ROW_NUMBER()over(partition by dw order by dt) as RN
 from CTE
)t
pivot(
Max(dt)
for dw in ([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday])
) as Pt Option (MAXRECURSION 31)

The problem is that, pivot is not considering the last day of the month for some reason as you can see in the screenshot.

Answer 1

You need to "group by" something unique to the row. In your case, since you want one row per week, that would be the week number.¹ In a pivot table, that "grouping" is achieved by just adding the relevant field to your data source:

SELECT [Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday]
  FROM (SELECT dt, dw, DATEPART(wk, dt) AS week_nr
          FROM #tbl
       ) AS t
 PIVOT (MAX(dt)
        FOR dw in ([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday])
       ) AS pivotTable

Result:

Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
2022-05-01	2022-05-02	2022-05-03	2022-05-04	2022-05-05	2022-05-06	2022-05-07
2022-05-08	2022-05-09	2022-05-10	2022-05-11	2022-05-12	2022-05-13	2022-05-14

Fiddle: http://sqlfiddle.com/#!18/cdf3e1/6/0

---

¹ Obviously, if you want your calendar to span multiple years, you'll need the "week year" as well.

Answer 2

Got, the solution, the last day was missing because of the where statement in my CTE, it should be less than or equals to max date.

    With CTE (dt,dw,last)
as
(
select Cast(dateadd(day, -day(GetDate())+1,GetDate())as date),datename(dw,Cast(dateadd(day, -day(GetDate())+1,GetDate())as date)),
cast(dateadd(day,-1,dateadd(Month,1,Cast(dateadd(day, -day(GetDate())+1,GetDate())as date))) as date)
union all
select cast(dateadd(day,1,dt) as date), DATENAME(DW,cast(dateadd(day,1,dt) as date)),last
from CTE
where cast(dateadd(day,1,dt) as date)<=last
)
select * from (
select dt,dw,ROW_NUMBER()over(partition by dw order by dt) as RN
 from CTE
)t
pivot(
Max(dt)
for dw in ([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday])
) as Pt Option (MAXRECURSION 31)