How to show all day name of a month as a column using a SQL query to show that in a report

Question

While creating some report I need to come up with a query which takes the month and year as parameter and gives me a result where all the name of the days are as columns.

For example:

Idea is to cross join with my existing dataset and present that in the report. Again if there is any other way to show this kind of data in the RDL report that would be fine also.

Answer 1

You can use cte recursion make a Calendar table for this month.

• datename get columns name.
• day get month day number.

then connected condition aggregate function SQL string for the pivot.

final, dynamic SQL executed the SQL dynamically.

DECLARE @col AS NVARCHAR(MAX) ='',
        @query  AS NVARCHAR(MAX);

;WITH Calendar AS(
    SELECT  dateadd(month,datediff(month,0,getdate()),0) startdate,dateadd(month,datediff(month,0,getdate()),31) enddate
    UNION ALL
    SELECT startdate + 1 , enddate
    FROM Calendar 
    WHERE startdate + 1 < enddate
), CalendarPivot as (
 SELECT datename(weekday,startdate) dayname,
           day(startdate) daynum
 FROM Calendar
)

SELECT  @col = @col + 'MAX(CASE WHEN daynum = '+cast(daynum as varchar(5))+' THEN daynum END) '+ dayname +',' 
FROM CalendarPivot t1

set @col = substring(@col,0,len(@col))


set @query = '
;WITH Calendar AS(
    SELECT  dateadd(month,datediff(month,0,getdate()),0) startdate,dateadd(month,datediff(month,0,getdate()),31) enddate
    UNION ALL
    SELECT startdate + 1 , enddate
    FROM Calendar 
    WHERE startdate + 1 < enddate
), CalendarPivot as(
 SELECT datename(weekday,startdate) dayname,
           day(startdate) daynum
 FROM Calendar
)
SELECT ' + @col + ' 
from CalendarPivot'

execute(@query)

sqlfiddle

NOTE

getdate() can change to use your parameter.