Declare @SQL varchar(max) = 'Select NOM_EMP as [X],PRENOM_EMP as [Y],' + (Select Stuff((Select ',[' +cast(N as varchar(25))+']=''''' From (Select Top (31) N=Row_Number() Over (Order By (Select null)) From master..spt_values n1) A For XML Path ('')) ,1,1,'') ) + ' FROM EMPLOYE' Exec(@SQL)

What I'm doing here is loading employees names + number of days in the current month (31 in this case). The above query will result in something like this:

The employe table is related (EMPLOYE_ID) to ,say Requests table, each request have a start_date and end_date .

What I want to achieve is to have the letter "R" written If start_date and end_date day number matches the day number in the cell.

If a request is made (by the first employe) with this info: 08-10-2018 and 08-14-2018

The table should look like this:

Is that something realistic to achieve? If so, any help is appreciated.

EDIT:

Declare @D1 datetime = '2018-08-01' Declare @D2 datetime = '2018-08-31' Declare @Cols varchar(max) = (Select Stuff((Select ',[' +cast(N as varchar(25))+']' From (Select Top (DateDiff(DAY,@D1,@D2)+1) N=Row_Number() Over (Order By (Select Null)) From master..spt_values n1) A For XML Path ('')),1,1,'') ) Declare @SQL varchar(max) = ' Declare @D1 date = '''+cast(@D1 as varchar(50))+''' Declare @D2 date = '''+cast(@D2 as varchar(50))+''' Select * From ( Select firstName,LastName, Item = day(d) ,Value = case when D between DEBUT_DRC and FIN_DRC then ''R'' else '''' end From DEMANDE_RECUPERATION DC INNER JOIN EMPLOYE E ON DC.MAT_EMP = E.MAT_EMP Cross Join ( Select Top (DateDiff(DAY,@D1,@D2)+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@D1) From master..spt_values n1 ) B ) src Pivot (max(value) for Item in ('+@Cols+') ) pvt ' Exec(@SQL)

Output: