title: Multiple aggregate & customize value with Pivot query in SQL
body: <p>I am building pivot query to display data in report form as below.</p>
  
  <pre><code>CREATE TABLE #Table1 (ColId INT)
  INSERT INTO #Table1 VALUES(29)
  INSERT INTO #Table1 VALUES(49)
  INSERT INTO #Table1 VALUES(59)
  
  
  DECLARE @cols NVARCHAR(2000)
  SELECT  @cols = COALESCE(@cols + ',[' + Convert(varchar(10), ColId)  + ']',
                           '[' + Convert(varchar(10), ColId) + ']')
  FROM    #Table1
  print (@cols)
  
  Declare @final NVARCHAR(2000)
  set @final=
  'SELECT *
  FROM (
  SELECT tv
          , rv
          , dpv
  FROM (
  
        ---Inner join query with multiple tables ----    
  
  ) As C
  ) P
  PIVOT (
  Max(dpv)
  FOR tv IN (' + @cols + ')
  ) AS PVT'
  
  exec(@final)
  
  
  drop table #Table1
  </code></pre>
  
  <p>and result of above query as below.</p>
  
  <p><img src="https://i.stack.imgur.com/7UtbG.png" alt="enter image description here"></p>
  
  <p>I would like result as below</p>
  
  <p><img src="https://i.stack.imgur.com/sKPNX.png" alt="enter image description here"></p>
  
  <p>In this i need to append 'C' in tv so there would be 2 column 29C &amp; 29 and under 29C i need to display 29C + dpv and under 29 i need to display summation of tv + dpv (i.e. 29+ 5=34) like that for all column. </p>
  
  <p>How i can achieve it? How can we add multiple aggregate value?
  thanks in advance.</p>