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.

CodePudding user response：

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:

Fiddle: