title: Row-wise sum of dynamically generated columns in SQL
body: <p>This is the procedure of generating monthly attendance report of a class where I want to add one more column as "Total", which should contain sum of the attendance of a particular student between the dates, given as a parameter.</p>
  
  <p>So what should I do to achieve that as simply doing sum(Attendance) will do the sum of attendance of all dates present in the table ?</p>
  
  <pre><code>CREATE PROCEDURE GET_ATTENDANCE_REPORT_FOR_FACULTY
  @startdate DATE,  
  @enddate DATE,
  @coursecode nvarchar(10),
  @subjectcode nvarchar(10)
  
  AS BEGIN
  
  DECLARE @query as varchar(MAX);
  declare @stmt nvarchar(max);
  declare @stmt1 nvarchar(max);
  
  with cte (startdate) as 
  (
      select @startdate startdate
      union all 
      select dateadd(DD, 1, startdate) 
      from cte
      where startdate &lt; @enddate
  )
  
  select @query = coalesce(@query, '') + 
                N',coalesce(MAX(CASE when A.[Date] = ''' + 
                cast(cte.startdate as nvarchar(20)) + 
                N''' THEN Convert(varchar(10),A.[Attendance]) end), ''NA'') ' + 
                quotename(convert(char(6), cte.startdate,106))
  from cte
  where datename(weekday, cte.startdate) &lt;&gt; 'Sunday';
  
  set @query = 'Select S.RollNo AS [Roll No],Concat(FirstName,'' '',LastName) AS Name' + @query + ',sum(Attendance) Total
            from Attendance A, Student S, UserDetails U
            where A.EnrollmentNo=S.EnrollmentNo and S.EnrollmentNo=U.userID and A.CourseCode=''' + @coursecode + ''' and A.SubjectCode =''' + @subjectcode + '''
            and A.Date between ''' + @startdate + ' and ' + @enddate + '''
            Group By S.RollNo,U.FirstName,U.LastName';
  
  
  Execute (@query)
  END
  </code></pre>