Search code examples
sqlsql-server-2008t-sqlcalculated-columnsdynamic-columns

Calculate columnwise total for dynamic columns in SQL Server

I have the following output which is generated from stored procedure which is the following

   SET @sql = '

   SELECT  * FROM (SELECT [NAMES],logdate,[worked time] 
   from tmp_phys_table GROUP BY NAMES,LOGDATE,[WORKED TIME]) AS [SubTable] 
   PIVOT
   (
    MAX([worked time])
             FOR [logdate] IN ([' + @docDates + '])
       ) AS [Pivot] ;';

The above query gives the following output

output datat

Now I want to do a columnwise total and include a new column called total time at the end of the columns which are dynamic columns eg. there may be 3 columns or 30 columns.

Solution

  • Should be something like that: 

       SET @sql = '

   SELECT *
   FROM (SELECT [NAMES], logdate, [worked time], [Total Time] 
         FROM tmp_phys_table 
         GROUP BY NAMES,LOGDATE,[WORKED TIME], [Total Time]
         ) AS [SubTable] 
   PIVOT
   (
    MAX([worked time])
             FOR [logdate] IN ([' + @docDates + '])
       ) AS [Pivot] ;';